[postgis-users] advice for quicker display?

[Stephen Woodbridge]

On 2/14/2012 2:32 PM, Bistrais, Bob wrote:
> OK, so it sounds like I need to create the index for starters (and not
> bother with the layer at full scale).
>
> As far as creating the index, is mytable_the_geom_gidx an existing
> column, or the name of a new one?

mytable_the_geom_gidx is a unique name for a particular index, I 
construct mine roughly using:

<tablename>_<column_name_to_index>_<idx|gidx> where idx is a standart 
btree index and gidx is a gist index. You can use any name you want as 
long as it is not in use already because you are using it to put the 
newly created index into.

Beyond giving it a name so you can later reindex, or drop it the name 
name does not really matter, as the SQL planner will find the index and 
use it if appropriate.

-Steve W

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Stephen V. Mather
> Sent: Tuesday, February 14, 2012 2:26 PM
> To: 'PostGIS Users Discussion'
> Subject: Re: [postgis-users] advice for quicker display?
>
> To expand on that, the basic understanding is that file level access to
> data
> (e.g. shapefile) will always be faster for the entire dataset (all
> things
> being equal) because of the management overhead of the database, but for
> accessing a subset of features of the dataset (often the typical use
> case),
> the database is faster if it has indices on the appropriate columns (the
> geometry being the more important one).  Even if you are accessing all
> of
> the records in the database at once as a common case, you probably don't
> need every node in the polygons/polylines, and might create a view or
> column
> that pre-generalizes those data before delivering them.
>
> Best,
> Steve
>
> Stephen Mather
> Geographic Information Systems (GIS) Manager
> (216) 635-3243
> svm at clevelandmetroparks.com
> clevelandmetroparks.com
>
>
>
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Stephen
> Woodbridge
> Sent: Tuesday, February 14, 2012 2:15 PM
> To: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] advice for quicker display?
>
> 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://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