Hi,
It is fast!
-Jukka-
________________________________________
Lähettäjä: Michaël Michaud [michael.mich...@free.fr]
Lähetetty: 30. lokakuuta 2011 22:35
Vastaanottaja: jump-pilot-devel@lists.sourceforge.net
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 [michael.mich...@free.fr]
> Lähetetty: 29. lokakuuta 2011 20:57
> Vastaanottaja: jump-pilot-devel@lists.sourceforge.net
> 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 [michael.mich...@free.fr]
>>> Lähetetty: 11. lokakuuta 2011 22:41
>>> Vastaanottaja: jump-pilot-devel@lists.sourceforge.net
>>> 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
>>>> Jump-pilot-devel@lists.sourceforge.net
>>>> 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
>>> Jump-pilot-devel@lists.sourceforge.net
>>> 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
>>> Jump-pilot-devel@lists.sourceforge.net
>>> 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
>> Jump-pilot-devel@lists.sourceforge.net
>> 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
>> Jump-pilot-devel@lists.sourceforge.net
>> 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
> Jump-pilot-devel@lists.sourceforge.net
> 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
> Jump-pilot-devel@lists.sourceforge.net
> 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
Jump-pilot-devel@lists.sourceforge.net
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
Jump-pilot-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
