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 <[email protected]> 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 <[email protected]> > *To:* Brent Wood <[email protected]> > *Cc:* "[email protected]" <[email protected]> > *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 <[email protected]> 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 <[email protected]> > *To:* [email protected] > *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 > [email protected] > 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/>*
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
