[postgis-users] sync geometry with attributes

[John Callahan]

This is great!  Thank you.  I never thought about creating a view and 
pointing some of our apps directly to the view instead of the table.  I 
will give this a try.

I'm currently trying to move my organization away from Oracle and into 
Postgres/PostGIS.  Our Oracle person (who is really a geologist doing 
the best he can!) has always had problems synchronizing the coordinates 
and attributes. If I can offer a solution through PostGIS, that would 
certainly strengthen my position.  (Of course, the same could be done 
with Oracle, but he doesn't need to know that!)

- John


****************************************
John Callahan
Geospatial Application Developer

Delaware Geological Survey
University of Delaware
227 Academy St, Newark DE 19716-7501
Tel: (302) 831-3584  

Email: john.callahan at udel.edu
http://www.dgs.udel.edu
****************************************



Obe, Regina wrote:
> John
>
> There are two ways of dealing with this
>
> 1) Put in a trigger in your table so that when the geometry gets edited,
> It updates your long = ST_X(the_geom)
> 	and your lat = ST_Y(the_geom)
>
> 2) The way I find easier to deal if you don't edit those fields directly
> is just to get rid of your
> long lat fields and create a view
>
> CREATE VIEW vwmytable
> AS 
> 	SELECT gid, field1, field2, field3, ST_X(the_geom) as long,
> ST_Y(the_geom) As lat
> 	FROM mytable
>
>  You can also put an update rule on your view so that if anyone tried to
> edit the long, lat fields
> then it will update the_geom field with something like
>
> the_geom = CASE WHEN OLD.long <> NEW.long THEN ST_MakePoint(long, lat,
> SRID(Old.the_geom)) ELSE NEW.the_geom END
>
> Hope that helps,
> R
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of John
> Callahan
> Sent: Friday, May 23, 2008 3:49 PM
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] sync geometry with attributes
>
> Please excuse my question if it seems too trivial.  I am just getting 
> started with PostGIS and couldn't quite find what I was looking for.
>
> For a point data set, it is common for us to store the lat/long (or 
> easting/northing) as attributes in the table. (Some programs we have 
> require this.)  However, when I edit the position through a GIS (like 
> QGIS), the spatial location is altered but obviously not the 
> attributes.  And when these attributes are altered in the table, the 
> geometry is not updated, obviously.   Is there a way to keep these 
> synchronized?  Is this built into PostGIS? 
>
> Thanks for your help.  Even is you don't know how this could be done, is
>
> it possible?
>
> - John
>
> ****************************************
> John Callahan
> Geospatial Application Developer
>
> Delaware Geological Survey
> University of Delaware
> 227 Academy St, Newark DE 19716-7501
> Tel: (302) 831-3584  
>
> Email: john.callahan at udel.edu
> http://www.dgs.udel.edu
> ****************************************
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> -----------------------------------------
> 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.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>