[postgis-users] DB design considerations : geoAttributes vs geometry
Gerry Creager N5JXS
gerry.creager at tamu.edu
Tue Apr 12 06:56:04 PDT 2005
I do not have an answer (although one of my grad students might, and I'm
meeting with him shortly) but I am very interested in any answers you
might get. We're doing something similar in Texas (and eventually the
US and off-shore) for weather, oceanographic and hydrology data.
Regards,
Gerry Creager
Texas Mesonet
Moisan Yves wrote:
> Hi All,
>
> Maybe a bit OT, but I have this basic DB design question. I have a
> bunch of data sources (sensors, people ...) that will dump data in
> postGIS. I am wondering what the best trade-off is between redundant
> attribute data on which I could build indexes and just using the_geom to
> build an index.
>
> Example :
>
> Station 1 : the_geom; [geoInfoColumns] (redundant attribute data);
> dataColumns
>
> Where
>
> - geoInfoColumns = string {country; province; adminRegion; city ...}
>
> - dataColumns = the real data
>
> The_geom and dataColumns are hard data. The question is about optional
> geoInfoColumns that I put in square brackets. When I upload a new
> station location, is it worth adding geoInfoColmuns *to use in filters*
> or can I rely on the_geom only ?
>
> The point is that I can make an arbitrary number of such optional
> sub-regions available that I could index on, e.g. "select * from table
> where sub-sub-sub-region = 'aRegion'", even though those optional
> columns really represent an over-determination in the sense that they
> are not needed to locate the station : they would be used to speed up
> access.
>
> I am not asking to what level the over-determination can go. That I can
> play with. I am just asking if making the DB much bigger by adding
> redundant information in the form of geoInfoColumns attributes is worth
> it in terms of access time. I would rather write utilities to translate
> queries filtered by sub-region so that they map on the_geom (e.g. have
> some table or function or stroed proc that returns a polygon when I feed
> it the region name) rather than add an arbitrary number of attributes
> (plus I would avoid being tied to geographical names that are very
> likely to change with time, e.g. a political riding or some other
> administrative region!).
>
> Thanx for any (basic!) pointers.
>
> Yves Moisan
>
>
> _______________________________________________
> 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