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

KhunSanAung khunsanaung.gis at gmail.com
Wed Apr 29 01:22:07 PDT 2015


Thank you very much Brent.
Yes, I used postGIS, QGIS, and GeoServer to create the complet apps.
With your good example, I am able to create the UPDATE command and I could
also create the TRIGGER function on the database.
The apps works well now.
Many thanks again
Best regards

On Wed, Feb 4, 2015 at 2:24 AM, Brent Wood <pcreso at pcreso.com> wrote:

> 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*
> * <http://geoportal.icimod.org/>*
>
> _______________________________________________
> 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*
> * <http://geoportal.icimod.org/>*
>
>
>


-- 
Have a nice day!
--

*Mr. Khun San Aung*
* <http://geoportal.icimod.org/>*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150429/774e267c/attachment.html>


More information about the postgis-users mailing list