[postgis-users] Convert from Lat/Long point to postGIS geometry

[Brent Wood]

I recommend you use QGIS to visualise your Postgis data & ensure it is correct before using Geoserver; QGIS & Postgis work very well together.
Postgis can help with the automatic populating of the data. You can create an on insert (or update) trigger in Postgres which will populate the missing column(s) whenever a record is inserted (or updated). A simple scrupt that does this is below - just a series of SQL's to illustrate this. Note that if you update a record (change x or y values) then the point will be in the wrong place, it needs updating as well. Ideally you should create an update & insert before function to replace the insert/update with a new one doing the full job... but this will hopefully illustrate how you might go about this.
If all your inserts/updates are done programatically rather than manually, then you may be able to modify the program to do this without using the db to automate it.
Cheers
  Brent


#! /bin/bash
# script to create database, install postgis, and create:
#   a table with a geometry column & x,y columns
#   a trigger function to update the table geometry column,
#        populating null geometries with a geometry made from coords 
#   a trigger invoking the function on update
# run a couple of inserts to test it works
# look at the result

dropdb test
createdb test
psql -d test -c "create extension postgis;"
psql -d test -c "create table test_trigger 
                   (id    serial primary key,
                    x     decimal(7,4),
                    y     decimal(7,4),
                    geom  geometry(point, 4326));"

psql -d test -c "CREATE OR REPLACE Function update_geom() RETURNS TRIGGER AS 
                 \$\$
                 BEGIN 
                   UPDATE test_trigger SET geom = ST_SetSRID(ST_Makepoint(x,y),4326) where geom isnull;
                   RETURN null;
                 END;
                 \$\$ 
                 LANGUAGE 'plpgsql';"

psql -d test -c "CREATE TRIGGER geom_trigger AFTER INSERT ON test_trigger FOR EACH ROW EXECUTE PROCEDURE update_geom();"

psql -d test -c "insert into test_trigger (x, y) values (179.0, -45.0);"
psql -d test -c "insert into test_trigger (x, y) values (179.5, -45.3);"
psql -d test -c "select id, x, y, ST_AsText(geom) from test_trigger;"



The result of running this is:
CREATE EXTENSION
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
INSERT 0 1
 id |    x     |    y     |     st_astext      
----+----------+----------+--------------------
  1 | 179.0000 | -45.0000 | POINT(179 -45)
  2 | 179.5000 | -45.3000 | POINT(179.5 -45.3)
(2 rows)


      From: KhunSanAung <khunsanaung.gis at gmail.com>
 To: Brent Wood <pcreso at pcreso.com> 
Cc: "postgis-users at lists.osgeo.org" <postgis-users at lists.osgeo.org> 
 Sent: Tuesday, February 3, 2015 9:08 PM
 Subject: Re: [postgis-users] Convert from Lat/Long point to postGIS geometry
   
Hi Brent Wood,
Many thanks, it works.UPDATE public.town SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

I am using postGIS to store the data and using GeoServer for publishing the data to maps.
I'm thinking to use the GeoExplorer (from OpenGeo Suite) for digitizing and collecting the location information.When using GeoExplorer, the geometry information is automatically stored to the geom field of the table and the use have to fill all the attribute again.
But I already have the full list in a postGIS table.How can I make my application in such a way that user just need to select from the list and digitizing the location only. No need to enter the attribute again.
Many thanks for any  idea.
Best regards




On Tue, Feb 3, 2015 at 10:50 AM, Brent Wood <pcreso at pcreso.com> wrote:

Hi.
Try something like:

update <table> set <geometry column> = ST_SetSRID(ST_MakePoint(Longitude, Latitude),4326);
Essentially create a point geometry from your numeric values, with the ST_MakePoint() function, the inform Postgis it is a standard lat/long CS (EPSG:4326 - which you should have specified when you created the column), & update the table with these values for each row. Make sure you use your table & column names....
What mapping/GIS program are you using?

Cheers,
  Brent Wood

      From: KhunSanAung <khunsanaung.gis at gmail.com>
 To: postgis-users at lists.osgeo.org 
 Sent: Tuesday, February 3, 2015 5:11 PM
 Subject: [postgis-users] Convert from Lat/Long point to postGIS geometry
   
Hi All,
I have one table (Town info) in postgres without Geometry field.I have Latitude and Longitude information for those points data separately (collecting & filling).

I created the postGIS extension and add the Geometry field in the above postgres table.Now, I'd like to add the location information into the postGIS geometry field so that I can immediately view those points on the map. 
How can I convert the Latitude/Longitude value into postGIS geometry value?
Thank you very much in advance.
-- 
Have a nice day!
--
Mr. Khun San Aung
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

   



-- 
Have a nice day!
--
Mr. Khun San Aung

  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150203/5f37f5d4/attachment.html>