[postgis-users] Re: Enormous file geodatabase feature class

Obe, Regina robe.dnd at cityofboston.gov
Fri Mar 14 06:12:30 PDT 2008


 > Thanks Regina and Webb. Regina, do you actively use
> PostGIS for City of Boston GIS work?
Yes - well just one specific department (and even here there is a mix of
MapInfo and ESRI, but PostGIS/PostgreSQL serves better for analytical
stuff and integrating with other database and web apps) 
- the rest of the City 
departments are highly ESRI centric as I guess could 
be said about most city and federal government.


> Have you thought about using inherited tables and
> constraint exclusion. It has a couple of advantages
> over loading everything in one table
> 1) Your master table can have fewer fields than the
> other tables, that way you can have the core fields
> in master and still maintain some of
> the other fields for the counties that vary.

> Haven't thought about it, but thanks to you I now
> am--thanks for the suggestion. Is this something
> you've actively used? Can you give a real-world
> example? Have any others here used inherited tables in
>  this way, and can you give an example?

I've used a couple of times - for here - we have a ParcelTime master
table where
each years parcels are stored in a separate table - think we have about
6 years worth - not amounting to much (as far as GIS is concerned) - but
comes in handy as you can easily geocode projects that were against
parcels that have been destroyed or reparcelized (often as a result of
the project) or figure out when a parcel last existed with a query like 

SELECT DISTINCT ON(pid)
	PID,pid_year,  the_geom 
	FROM parceltime
	ORDER by pid, pid_year DESC

Which surprisingly runs faster than I would expect

(or figure out what it was folded or built out of)

But yet doesn't impact speed for looking at latest year data since we
have a constraint on the year field. (So this is partitioned by time
rather than space)

Also using inherited tables for a non-city project among other things -
I think closer
to what you are doing (which does use space partitioning).  

For both of these - the unheritability of foreign key constraints don't
play too much of an issue and in the situation like where Webb
mentioned, I usually have paired tables so there are workarounds for
that issue at anyrate.

Hope that helps,
Regina

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.




More information about the postgis-users mailing list