[postgis-users] Large Databases

[Graham Stewart]

Mark,

That sounds like a good idea.

How will the partitioning work with spatial queries? If I partition by
state and then try to select some data which straddles state boundaries
will I have to explicitly pass in those states?

This is going to be the backend for a desktop application used by a
handful of users. I'd rather have a simpler set up than get every last
ounce of performance out of the system. It won't be a huge inconvenience
if we have to take the system offline for a weekend to do the quarterly
updates to the map data.

Graham

mark thomas wrote:
> Consider using table partitioning by state, zip code, county, or
> something similar to help speed up queries.  Also, when loading your
> data, I found it helpful to make sure I vacuumed/analyzed the DB after
> every state load; otherwise, it took forever trying to gather statistics
> on the entire U.S detailed streets dataset.  
>
> When running queries against your geometries using geometry relationship
> functions such as Intersects(), Within(), etc. make sure you filter out
> geometries in the table not relating to the input geometry by using a
> spatial operator, which will make use of your spatial index.  Using the
> envelope of the input geometry when using a spatial operator will
> greatly speed up your queries as well.
>  
> On Fri, 2006-06-09 at 12:00 -0700,
> postgis-users-request at postgis.refractions.net wrote:
>
>   
>> [postgis-users] Large Databases
>>     
>
> Mark Thomas
> Senior Software Engineer
> Awarix Corporation
> mthomas at awarix.com
> http://www.awarix.com
>
> "Commit to the Lord whatever you do, 
>     and your plans will succeed." - Proverbs 16:3
>
>
>
>   
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>