[postgis-users] optimal database design question

[Markus Schaber]

Hi, Matthew,

On Tue, 15 Jun 2004 05:48:14 -0700 (PDT)
Matthew Perry <mtperry78 at sbcglobal.net> wrote:

>   So I've got 6 shapfiles (representing counties, streams, roads,
>   towns, places and USGS Quad boundaries)  that I'm trying to bring
>   into PostGIS. The user will specify one of the polygon features. My
>   goal is to perform a spatial query selecting ALL other features that
>   fall within the specified polygon. 
>  
> My question is: Should I put the features from all 6 shapfiles into a
> single table, mixing points, lines and polys into one huge table with
> a relatively simple SQL query. OR would it be more efficient to have 6
> tables and write a more complex query (or multiple queries?) Has
> anyone evaluated these two methods based on execution speed and/or
> ease of data import?

You should notice that some tools (AFAIR, Thuban was one of those) fail
to read columns of type Geometry, thus cannot work with such mixed
columns. But if you don't need those tools, this should be no problem.

If you decide to store the shapefiles in different tables, you could
also create a View to simplify the Query.

HTH,
Markus Schaber
-- 
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios at logi-track.com | www.logi-track.com