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
