[postgis-users] advice for quicker display?

Bistrais, Bob Bob.Bistrais at maine.gov
Wed Feb 15 11:58:37 PST 2012 

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

-- 

  ,------o-. 
  |   __/  |    Delivering high quality PostGIS 2.0 !
  |  / 2.0 |    http://strk.keybit.net
  `-o------'

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

More information about the postgis-users mailing list