[postgis-users] sync geometry with attributes

Obe, Regina robe.dnd at cityofboston.gov
Fri May 23 13:09:51 PDT 2008


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.




More information about the postgis-users mailing list