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....@gmail.com>
To: Brent Wood <pcr...@pcreso.com>
Cc: "postgis-users@lists.osgeo.org" <postgis-users@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 <pcr...@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....@gmail.com>
To: postgis-users@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
--
Have a nice day!
--
Mr. Khun San Aung
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
