Menu
▾
▴
Re: [JPP-Devel] Add feature count limit to dynamic PostGIS layers
|
From: Rahkonen J. <Juk...@mm...> - 2011-10-30 21:09:08
|
Hi,
I wonder if find_extents could be used only once per session, with the standard not updating PostGIS driver it will always return the same result, do you agree?
-Jukka-
________________________________________
Lähettäjä: Michaël Michaud [mic...@fr...]
Lähetetty: 30. lokakuuta 2011 22:35
Vastaanottaja: jum...@li...
Aihe: Re: [JPP-Devel] Add feature count limit to dynamic PostGIS layers
Hi Jukka,
Thanks for tests. Very precise, as usual !
I just committed the change from find_extent to ST_Estimated_Extent.
Should improve performance, but I'm not sure ST_Estimated_Extent is as
reliable as find_extent.
OpenJUMP comment says : estimated_extent sometimes return null.
Seems that ST_Estimated_Extent needs vacuum or analyze to return a valid
value.
With my small test database and ST_Estimated_Extent, I get error
messages with some tables and not with others...
Let's test with ST_Estimated_Extent a few days.
I'll try to add a patch to switch to find_extent in case
ST_Estimated_Extent fails.
Michaël
Le 30/10/2011 18:49, Rahkonen Jukka a écrit :
> Hi,
>
> I did not remember at all that ticket but it is indeed still a problem. See below the SQL requests which are send when moving on the map. There are three requests per layer: find the extent, find some metadata and finally get the data.
>
> 1. SELECT AsBinary(find_extent( 'osm_line', 'way' ));
> 2. SELECT AsBinary(find_extent( 'osm_polygon', 'way' ));
>
> 3. SELECT column_name FROM information_schema.columns WHERE lower(table_schema) = 'public' AND lower(table_name) = 'osm_line';
> 4. SELECT column_name FROM information_schema.columns WHERE lower(table_schema) = 'public' AND lower(table_name) = 'osm_polygon';
>
> 5. SELECT AsEwkb("way") as way,"osm_id","note","source","access","addr:housenumber","addr:street","addr:postcode","addr:city","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","bridge","boundary","building","construction","craft","cutting","disused","embankment","foot","highway","historic","horse","junction","landuse","layer","learning","leisure","lock","man_made","maxspeed","military","motorcar","name","natural","office","oneway","operator","power","power_source","place","railway","ref","religion","residence","route","service","shop","sport","surface","tourism","tracktype","tunnel","waterway","width","wood","z_order","way_area","tags","priority" FROM "osm_line" t WHERE "way"&& SetSRID('BOX3D(331986.16357747343 6904187.263884243,335072.0738679022 6907136.144410443)'::box3d,3067) AND highway is not null LIMIT 100;
>
> 6. SELECT AsEwkb("way") as way,"osm_id","note","source","access","addr:housenumber","addr:street","addr:postcode","addr:city","admin_level","aerialway","aeroway","amenity","area","barrier","bicycle","bridge","boundary","building","construction","craft","cutting","disused","embankment","foot","highway","historic","horse","junction","landuse","layer","learning","leisure","lock","man_made","maxspeed","military","motorcar","name","natural","office","oneway","operator","power","power_source","place","railway","ref","religion","residence","route","service","shop","sport","surface","tourism","tracktype","tunnel","waterway","width","wood","z_order","way_area","tags" FROM "osm_polygon" t WHERE "way"&& SetSRID('BOX3D(331986.16357747343 6904187.263884243,335072.0738679022 6907136.144410443)'::box3d,3067) LIMIT 100;
>
> Here are my timings:
> 1. 24612 ms
> 2. 26762 ms
> 3. 67 ms
> 4. 67 ms
> 5. 84 ms
> 6. 160 ms
>
> Timings when finding the estimated extents with SELECT AsBinary(estimated_extent( 'osm_line', 'way' ));
> 1. with estimated_extent: 5 ms
> 2. with estimated_extent: 4 ms
>
> Huge difference, isn't it? I would recommend to consider at least a possibility to have an alternative for selecting estimated extents. I think that the main drawback is that estimated extents are not always right, but OpenJUMP is probably checking them just for making a decision if queries 2 and 3 are made at all. With my data it is wasting 24 seconds for doing a query which could sometimes lead to a saving of about 0.2 seconds.
>
> -Jukka Rahkonen-
>
>
>
> ________________________________________
> Lähettäjä: Michaël Michaud [mic...@fr...]
> Lähetetty: 29. lokakuuta 2011 20:57
> Vastaanottaja: jum...@li...
> Aihe: Re: [JPP-Devel] Add feature count limit to dynamic PostGIS layers
>
> Hi Jukka,
>
> There is a ticket
> https://sourceforge.net/tracker/?func=detail&aid=1944638&group_id=118054&atid=679909
>
> and a mail
> https://sourceforge.net/mailarchive/forum.php?thread_name=6B1D057DB7670E4CA686E2A33445D49F3CD2FF%40SRVEXCHANGE2003.agenzia.dom&forum_name=jump-pilot-devel
>
> from you about huge postgis table management in OpenJUMP
>
> This is only partly resolved with the maxFeature parameter.
> Would you check if the use of find_extent( 'table', 'geom' ) is still a
> problem ?
> I think this is used to check if the remote table intersects the view,
> but I'm not sure.
>
> Michaël
>
> Le 29/10/2011 16:46, Rahkonen Jukka a écrit :
>> Hi,
>>
>> Works fine for me. Also by saving the Max Features into project file and opening the project. This computer has Windows Vista 32-bit, jre 1.7.0 and PostgreSQL 8.3 with PostGIS 1.5. I tested only with the OJ native PostGIS connector.
>>
>> -Jukka Rahkonen-
>>
>> Michaël Michaud wrote:
>>
>>> Hi Jukka, all,
>>> The maxFeature parameter should be available from NB (svn 2500).
>> Now, addDatastore plugin should also be able to preserve z values.
>>
>>> Please, test and report any problem,
>>> I did not test anything else than PostGIS Driver.
>>> Would be useful to test other plugins.
>> Michaël
>>
>> Le 11/10/2011 22:59, Rahkonen Jukka a écrit :
>>> Hi,
>>>
>>> I would say that the limit should be per query. General option for everything or even per connection is too course. Points are not as heavy as polygons, features with few attributes easier than those loaded with attributes. Even using the WHERE filter for the same database table can make difference. Short and simple ditches may come from the same waterway table than bir rivers. Perhaps it could be just one more selection in Data Store Layer dialogue. Now we have Connection; Dataset; Geometry; Where and the new one could be Limit:
>>> Limit is a PostGIS word but officially we do not have other data store drivers which integrates with the native PostGIS one. I know one exception: SIS Oracle driver makes a new Data Store and Oracle is using "WHERE rownum<x" instead of "limit x". Therefore users of SIS db plugin might wonder why limit does not work. Documentation should help in this situation if there is nobody to fix the SIS plugin.
>>>
>>> -Jukka-
>>>
>>>
>>> ________________________________________
>>> Lähettäjä: Michaël Michaud [mic...@fr...]
>>> Lähetetty: 11. lokakuuta 2011 22:41
>>> Vastaanottaja: jum...@li...
>>> Aihe: Re: [JPP-Devel] Add feature count limit to dynamic PostGIS layers
>>>
>>> Hi Jukka,
>>>
>>>> What do you think, would it be too dangerous to add a possibility to set a feature count limit for PostGIS datastore layers? I know I can set scale limit for the layer but if I have zoomed to show the whole country when adding some layer with millions of features into OpenJUMP is starts to read the whole PostGIS table and chokes before it is possible to set the scale limit.
>>> Interesting,
>>>> What is dangerous is that user would not always have all the data from the PostGIS layer on the visible map. Perhaps there should be a red light burning and warning the user that there is a count limit set for the layer? Even better if the light burns only if the feature limit has been reached. Technically it should not be difficult to set the feature count, just to add "limit [max_features]" to all the SQL queries if the parameter is set.
>>> Where do you thing the parameter would have to be set : general option
>>> (option panel), per connection, or per query ?
>>> About the danger of not having all the data in the visible layer, I
>>> think the dynamic datastore management is already dangerous.
>>> Running a process against a layer will make you feel you 're running it
>>> against the whole postgis table, but instead, you will run it against
>>> the visible part of the table only.
>>> That said, red light would be a plus for the use case you are describing.
>>>
>>> Michaël
>>>> -Jukka Rahkonen-
>>>> ------------------------------------------------------------------------------
>>>> All the data continuously generated in your IT infrastructure contains a
>>>> definitive record of customers, application performance, security
>>>> threats, fraudulent activity and more. Splunk takes this data and makes
>>>> sense of it. Business sense. IT sense. Common sense.
>>>> http://p.sf.net/sfu/splunk-d2d-oct
>>>> _______________________________________________
>>>> Jump-pilot-devel mailing list
>>>> Jum...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>>>>
>>>>
>>> ------------------------------------------------------------------------------
>>> All the data continuously generated in your IT infrastructure contains a
>>> definitive record of customers, application performance, security
>>> threats, fraudulent activity and more. Splunk takes this data and makes
>>> sense of it. Business sense. IT sense. Common sense.
>>> http://p.sf.net/sfu/splunk-d2d-oct
>>> _______________________________________________
>>> Jump-pilot-devel mailing list
>>> Jum...@li...
>>> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>>>
>>> ------------------------------------------------------------------------------
>>> All the data continuously generated in your IT infrastructure contains a
>>> definitive record of customers, application performance, security
>>> threats, fraudulent activity and more. Splunk takes this data and makes
>>> sense of it. Business sense. IT sense. Common sense.
>>> http://p.sf.net/sfu/splunk-d2d-oct
>>> _______________________________________________
>>> Jump-pilot-devel mailing list
>>> Jum...@li...
>>> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>>>
>>>
>> ------------------------------------------------------------------------------
>> Get your Android app more play: Bring it to the BlackBerry PlayBook
>> in minutes. BlackBerry App World™ now supports Android™ Apps
>> for the BlackBerry® PlayBook™. Discover just how easy and simple
>> it is! http://p.sf.net/sfu/android-dev2dev
>> _______________________________________________
>> Jump-pilot-devel mailing list
>> Jum...@li...
>> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>>
>> ------------------------------------------------------------------------------
>> Get your Android app more play: Bring it to the BlackBerry PlayBook
>> in minutes. BlackBerry App World™ now supports Android™ Apps
>> for the BlackBerry® PlayBook™. Discover just how easy and simple
>> it is! http://p.sf.net/sfu/android-dev2dev
>> _______________________________________________
>> Jump-pilot-devel mailing list
>> Jum...@li...
>> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>>
>>
>
> ------------------------------------------------------------------------------
> Get your Android app more play: Bring it to the BlackBerry PlayBook
> in minutes. BlackBerry App World™ now supports Android™ Apps
> for the BlackBerry® PlayBook™. Discover just how easy and simple
> it is! http://p.sf.net/sfu/android-dev2dev
> _______________________________________________
> Jump-pilot-devel mailing list
> Jum...@li...
> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>
> ------------------------------------------------------------------------------
> Get your Android app more play: Bring it to the BlackBerry PlayBook
> in minutes. BlackBerry App World™ now supports Android™ Apps
> for the BlackBerry® PlayBook™. Discover just how easy and simple
> it is! http://p.sf.net/sfu/android-dev2dev
> _______________________________________________
> Jump-pilot-devel mailing list
> Jum...@li...
> https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
>
>
------------------------------------------------------------------------------
Get your Android app more play: Bring it to the BlackBerry PlayBook
in minutes. BlackBerry App World™ now supports Android™ Apps
for the BlackBerry® PlayBook™. Discover just how easy and simple
it is! http://p.sf.net/sfu/android-dev2dev
_______________________________________________
Jump-pilot-devel mailing list
Jum...@li...
https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
|