[postgis-users] sync geometry with attributes
John Callahan
john.callahan at UDel.Edu
Fri May 23 13:20:06 PDT 2008
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
>
More information about the postgis-users
mailing list