Re: [Qgis-user] postgis / PHP

Micha Silver Thu, 09 Feb 2012 09:55:17 -0800

On 02/09/2012 07:19 PM, uk52rob wrote:

Thanks Andreas, I will give that a go.

To expand on Andreas' suggestion here's a PostGIS trigger function I use to automatically update columns for Lon/Lat and the X-Y coords for each insert of a new point:

-- Function: update_lonlat_coords()
-- DROP FUNCTION update_lonlat_coords();

CREATE OR REPLACE FUNCTION update_lonlat_coords()
RETURNS trigger AS
$BODY$
DECLARE g integer;
BEGIN
    g:=NEW.gauge_id;
    UPDATE rain_gauges
        SET x_coord=ST_X(the_geom), y_coord=ST_Y(the_geom)
        WHERE gauge_id=g;
    UPDATE rain_gauges
        SET lon=ST_X(ST_TRANSFORM(the_geom,4326)), lat=ST_Y(ST_TRANSFORM(the_geom,4326))
        WHERE gauge_id=g;
    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION update_lonlat_coords() OWNER TO flood_dba;

If you apply this trigger function ON INSERT, then each new point will automatically have lon/lat and the projected X-Y in those fields.

Regards,

Micha

Date: Thu, 09 Feb 2012 17:21:15 +0100

From: Andreas Neumann <[email protected]>

Subject: Re: [Qgis-user] postgis / PHP

To: <[email protected]>

Message-ID: <[email protected]>

Content-Type: text/plain; charset=UTF-8; format=flowed

Hi,

You can use the Postgis commands "ST_AsText()" and "ST_GeomFromText()"

for this purpose. If you want to extract x/y you can use

ST_AsText(ST_X(the_geom)) AS x.

Hope this helps? It is really easy - not complicated at all.

Andreas

This mail was received via Mail-SeCure System.
_______________________________________________
Qgis-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-user
This mail was received via Mail-SeCure System.

-- 
Micha Silver
GIS Consultant, Arava Development Co.
http://www.surfaces.co.il

_______________________________________________
Qgis-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] postgis / PHP

Reply via email to