[postgis-users] advice for quicker display?

Wed Feb 15 12:19:21 PST 2012

Yes, that is the way it works.

On 2/15/2012 3:16 PM, Bistrais, Bob wrote:
> OK, that seems to work by adding the map_bk_lot field.  If I needed to add more fields to the subquery, would I simply add them in with commas separating them? Example:
> (select gid, map_bk_lot, another_field1, another_field2, ST_Simplify(the_geom, 10.0)
>
>
> -----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 3:06 PM
> To: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] advice for quicker display?
>
> 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
>>
>
> _______________________________________________
> 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