[postgis-users] advice for quicker display?

Wed Feb 15 12:06:03 PST 2012

Actually it does NOT exist in the sub-query. Mapserver modifies the 
query because it needs the label text. So you sub-query needs to have 
ALL potential columns that you might need explicitly referenced.

DATA "the_geom from (select gid, map_bk_lot, ST_Simplify(the_geom, 10.0) 
as the_geom from mytable) as foo USING SRID=26919 USING Unique gid"

-Steve W

On 2/15/2012 2:58 PM, Bistrais, Bob wrote:
> Interesting- that works until I zoom in to about 1:50000 scale, then
> the layer  stops drawing with this error:
>
> prepare_database(): Query error. Error declaring cursor: ERROR:
> column "map_bk_lot" does not exist LINE 1: DECLARE mycursor BINARY
> CURSOR FOR SELECT "map_bk_lot"::text... ^
>
> With query string: DECLARE mycursor BINARY CURSOR FOR SELECT
> "map_bk_lot"::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
> from (select gid, ST_Simplify(the_geom, 10.0) as the_geom from
> parcels) as foo WHERE the_geom&&  setSRID('BOX3D(438251.727851473
> 4907493.5454546,445395.272148527 4910840.4545454)'::BOX3D, 26919 )
>
>
> ---Once again, the map_bk_lot column does indeed exist.  It will
> redraw fine when I zoom out again.
>
>
>
> -----Original Message----- From:
> postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Sandro Santilli Sent: Wednesday, February 15, 2012 2:50 PM To:
> PostGIS Users Discussion Subject: Re: [postgis-users] advice for
> quicker display?
>
> On Wed, Feb 15, 2012 at 02:26:19PM -0500, Bistrais, Bob wrote:
>> I tried this, but replacing mytable with parcels, it didn't draw,
>> the MapServer error log says "Query error. Error declaring cursor:
>> ERROR:  column "gid" does not exist LINE 1:
>> ...inary(force_collection(force_2d(the_geom)),'NDR'),gid::text
>> ..."
>>
>> -although the column gid does exist.
>
> You need to select it in the subquery:
>
> DATA "the_geom from (select gid, ST_Simplify(the_geom, 10.0) as
> the_geom from mytable) as foo USING SRID=26919 USING Unique gid"
>
> --strk;
>
>>
>>
>> -----Original Message----- From:
>> postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>> Stephen Woodbridge Sent: Wednesday, February 15, 2012 12:26 PM To:
>> postgis-users at postgis.refractions.net Subject: Re: [postgis-users]
>> advice for quicker display?
>>
>> DATA "the_geom from (select ST_Simplify(the_geom, 10.0) as the_geom
>> from mytable) as foo USING SRID=26919 USING Unique gid"
>>
>>
>> On 2/15/2012 11:03 AM, Bistrais, Bob wrote:
>>> OK, I am trying to use the ST_simplify command but I'm not doing
>>> it right.  I am using a layer called parcels, it has columns gid
>>> and the_geom, this is the DATA line from my MapServer map file:
>>>
>>> DATA "the_geom from (ST_Simplify(the_geom, 10.0)) USING
>>> SRID=26919 USING Unique gid"
>>>
>>> What am I doing wrong here?
>>>
>>> *From: * postgis-users-bounces at postgis.refractions.net
>>> [mailto:postgis-users-bounces at postgis.refractions.net] *On Behalf
>>> Of *Juan Marín Otero *Sent:* Tuesday, February 14, 2012 3:13 PM
>>> *To:* PostGIS Users Discussion *Subject:* Re: [postgis-users]
>>> advice for quicker display?
>>>
>>> You can find the documentation here. Also look at
>>> ST_SimplifyPreserveTopology, might be better depending on how
>>> strict you are with how your data gets modified.
>>>
>>> http://postgis.org/docs/ST_Simplify.html
>>>
>>> On Tue, Feb 14, 2012 at 3:09 PM, Bistrais,
>>> Bob<Bob.Bistrais at maine.gov <mailto:Bob.Bistrais at maine.gov>>
>>> wrote:
>>>
>>> Wow! Where are the ST_Simplify and other functions docuemented-
>>> in PostGIS docs or MapServer docs?
>>>
>>> *From: * postgis-users-bounces at postgis.refractions.net
>>> <mailto:postgis-users-bounces at postgis.refractions.net>
>>> [mailto:postgis-users-bounces at postgis.refractions.net
>>> <mailto:postgis-users-bounces at postgis.refractions.net>] *On
>>> Behalf Of *pcreso at pcreso.com<mailto:pcreso at pcreso.com> *Sent:*
>>> Tuesday, February 14, 2012 2:44 PM *To:* PostGIS Users
>>> Discussion *Subject:* Re: [postgis-users] advice for quicker
>>> display?
>>>
>>> Also,
>>>
>>> In Postgis you can use the ST_Simplfy() function in mapserver
>>> zoom (scale dependent) layers. So you have one dataset, and as
>>> you zoom out you display features represented by progressively
>>> fewer vertices. You don't need every headland of a global
>>> coastline dataset plotted until you until you zoom in. This can
>>> give vastly better performance than a shapefile when zoomed out.
>>>
>>> You can use pre-prepared geometries, so each record has more than
>>> one geometry column, or you can do the point reduction on the fly
>>> (in the SQL command in the mapfile DATA statement), whichever
>>> meets your needs.
>>>
>>> The default Postgres configuration is for pretty basic hardware.
>>> You might find&  run pgtune, or look at the postgres admin docs
>>> to configure it to more effectively use the available memory, and
>>> see how to use tablespaces to manage data&  indexes across
>>> filesystems to increase overall disk throughput.
>>>
>>> So it takes some work, but overall, Postgis provides spatial
>>> data management tools with substantial performance optimisation
>>> capabilities. But you need to use them.
>>>
>>> For plotting an entire unsimplified dataset, the overhead of a
>>> database will always be somewhat slower than a file streaming
>>> from disk. Note that on good hardware with an well optimised
>>> database even this difference can be minimised.
>>>
>>> Cheers,
>>>
>>> Brent Wood
>>>
>>> --- On *Wed, 2/15/12, Stephen Woodbridge
>>> /<woodbri at swoodbridge.com <mailto:woodbri at swoodbridge.com>>/*
>>> wrote:
>>>
>>>
>>> From: Stephen Woodbridge<woodbri at swoodbridge.com
>>> <mailto:woodbri at swoodbridge.com>> Subject: Re: [postgis-users]
>>> advice for quicker display? To:
>>> postgis-users at postgis.refractions.net
>>> <mailto:postgis-users at postgis.refractions.net> Date: Wednesday,
>>> February 15, 2012, 8:14 AM
>>>
>>> On 2/14/2012 1:57 PM, Bistrais, Bob wrote:
>>>> As some of you already know, I'm a newbie to PostGIS and have
>>>> just barely got a test PostGIS database up and running. So, my
>>>> latest question is in regards to draw times. I created a
>>>> statewide parcel layer from a shapefile. At statewide scale,
>>>> when the application loads, the parcel data draws far slower
>>>> than the original shapefile did. How can I tune this so that
>>>> draw times are better?
>>>
>>> If you are drawing all the data, it will always be slower than
>>> shapefiles. The trick is to not draw more than is reasonable at a
>>> given scale. So the answer is don't try to draw all the parcels
>>> for the whole state. As you zoom in and need to only draw a
>>> subset of the parcels, you will start to get speed advantages by
>>> using the spatial (GIST) indexes in postgis.
>>>
>>> create index mytable_the_geom_gidx on mytable using gist
>>> (the_geom);
>>>
>>> -Steve W _______________________________________________
>>> postgis-users mailing list postgis-users at postgis.refractions.net
>>> <http://mc/compose?to=postgis-users@postgis.refractions.net>
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>> _______________________________________________ postgis-users
>>> mailing list postgis-users at postgis.refractions.net
>>> <mailto:postgis-users at postgis.refractions.net>
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>>
>>> -- Juan Marín Otero GIS Consultant
>>>
>>> -------Visita mi blog en---------------------
>>> http://guachintoneando.blogspot.com
>>> ---------------------------------------------------
>>>
>>>
>>>
>>> _______________________________________________ postgis-users
>>> mailing list postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>> _______________________________________________ postgis-users
>> mailing list postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> _______________________________________________ postgis-users
>> mailing list postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>