[postgis-users] using "contains" in postgis

[John Taber]

Thanks for the explanations - the one time performance hit to use 
contains to add the container id field seems to be the way I'll approach 
it (btw I'm working with existing data files which is why it wasn't 
related in the first place).
John

Bill Binko wrote:
> John Taber wrote:
> 
>> I posted a question the other day regarding using "contains" with a 
>> join - no one addressed it so I'm wondering if it is a good way to go 
>> or should I be using another approach.  Basically I'm just trying to 
>> join an object with another object that is contained in it so 
>> attributes can be shared.  Thks for any help.
>>
> Well, you certainly _can_ do what you're suggesting.  The question is 
> _why_... if you can provide some insight into that, we can probably find 
> better solutions.
> 
> For example, let's say you want to show all of the information about a 
> parcel.  However, some of that information is maintained at the 
> census_tract level, and others are at the state level.
> 
> You CAN do this like this:
> 
> select parcels.*, states.state_level_data, tracts.tract_level_data  from 
> parcels, tracts, states where
>    tracts.tract_shape && parcels.parcel_shape AND
>    contains(tracts.tract_shape, parcels.parcel_shape) AND
>    state.state_shape && parcels.parcel_shape AND
>    contains(state.state_shape, parcels.parcel_shape) and parcel.key = 
> 'someKey';
> 
> However, it would almost always be a horrificly slow join.  (Note that 
> you must include the operators AND the contains() as only the operators 
> will use the GiST indexes -- and no, I don't know if other operators are 
> more effective -- anyone?)
> 
> You are much better off in this case (where you know the heirarchy of 
> your data in advance) to either:
> 1) Denormalize your data
> or
> 2) Create non-spatial foreign keys.
> 
> So, you'd either want to do something like this:
> ALTER TABLE parcles add column state_level_data;
> UPDATE parcels set state_level_data = (SELECT state_level_data from 
> states where state.state_shape && parcel_shape AND
>    contains(state.state_shape, parcel_shape LIMIT 1) ;  -- (assume you 
> will take the first if it's in multiple states... unlikey)
> 
> and something similar for tracts... then you'd have the attributes on 
> your parcels, and your queries would be faster since there would be no 
> joins at all.
> 
> OR
> 
> ALTER TABLE parcels ADD COLUMN state_id;
> UPDATE parcels set state_id = (SELECT state_id from states where 
> state.state_shape && parcel_shape AND
>    contains(state.state_shape, parcel_shape LIMIT 1) ; -- (should 
> proabably create foreign key constraint here, index, etc.)
> 
> Now, you stay normalized, but all of your joins are on non-spatial 
> fields.  You just have to update those links whenever your shapes move.
> 
> If you have dynamic data, these approaches get more "interesting", but 
> are still doable.  Postgresql has excellent rule and trigger mechanism 
> helping to keep this sane.
> 
> If your needs are more dynamic, or if there isn't such a strong 
> well-known heirarchy as this example, post the details (at the domain 
> level, not as a proposed solution) and we'll help you find one that works.
> 
> Bill
> 
> 
> 
> 
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
>