<div dir="ltr"><div class="gmail_default" style="color:#0000ff">Thank you very much Brent.</div><div class="gmail_default" style="color:#0000ff">Yes, I used postGIS, QGIS, and GeoServer to create the complet apps.</div><div class="gmail_default" style="color:#0000ff">With your good example, I am able to create the UPDATE command and I could also create the TRIGGER function on the database.</div><div class="gmail_default" style="color:#0000ff">The apps works well now.</div><div class="gmail_default" style="color:#0000ff">Many thanks again</div><div class="gmail_default" style="color:#0000ff">Best regards</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Feb 4, 2015 at 2:24 AM, Brent Wood <span dir="ltr"><<a href="mailto:pcreso@pcreso.com" target="_blank">pcreso@pcreso.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div style="color:#000;background-color:#fff;font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px"><div dir="ltr"><span>I recommend you use QGIS to visualise your Postgis data & ensure it is correct before using Geoserver; QGIS & Postgis work very well together.</span></div><div dir="ltr"><span><br></span></div><div dir="ltr"><span>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.</span></div><div dir="ltr"><br><span></span></div><div dir="ltr"><span>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.</span></div><div dir="ltr"><br><span></span></div><div dir="ltr"><span>Cheers</span></div><div dir="ltr"><br><span></span></div><div dir="ltr"><span> Brent</span></div><div dir="ltr"><br></div><div dir="ltr"><br></div><div dir="ltr"><br><span></span></div><div dir="ltr"><span>#! /bin/bash<br># script to create database, install postgis, and create:<br># a table with a geometry column & x,y columns<br># a trigger function to update the table geometry column,<br># populating null geometries with a geometry made from coords <br># a trigger invoking the function on update<br># run a couple of inserts to test it works<br># look at the result<br><br>dropdb test<br>createdb test<br>psql -d test -c "create extension postgis;"<br>psql -d test -c "create table test_trigger <br> (id serial primary key,<br> x decimal(7,4),<br> y decimal(7,4),<br> geom geometry(point, 4326));"<br><br>psql -d test -c "CREATE OR REPLACE Function update_geom() RETURNS TRIGGER AS <br> \$\$<br> BEGIN <br> UPDATE test_trigger SET geom = ST_SetSRID(ST_Makepoint(x,y),4326) where geom isnull;<br> RETURN null;<br> END;<br> \$\$ <br> LANGUAGE 'plpgsql';"<br><br>psql -d test -c "CREATE TRIGGER geom_trigger AFTER INSERT ON test_trigger FOR EACH ROW EXECUTE PROCEDURE update_geom();"<br><br>psql -d test -c "insert into test_trigger (x, y) values (179.0, -45.0);"<br>psql -d test -c "insert into test_trigger (x, y) values (179.5, -45.3);"<br>psql -d test -c "select id, x, y, ST_AsText(geom) from test_trigger;"<br><br></span></div><div dir="ltr"><br></div><div dir="ltr"><br></div><div dir="ltr">The result of running this is:</div><div dir="ltr"><br></div><div dir="ltr">CREATE EXTENSION<br>CREATE TABLE<br>CREATE FUNCTION<br>CREATE TRIGGER<br>INSERT 0 1<br>INSERT 0 1<br> id | x | y | st_astext <br>----+----------+----------+--------------------<br> 1 | 179.0000 | -45.0000 | POINT(179 -45)<br> 2 | 179.5000 | -45.3000 | POINT(179.5 -45.3)<br>(2 rows)<br><br><span></span></div><div dir="ltr"><span></span></div><br> <div style="font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px"> <div style="font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px"> <div dir="ltr"> <hr size="1"> <font face="Arial" size="2"> <b><span style="font-weight:bold">From:</span></b> KhunSanAung <<a href="mailto:khunsanaung.gis@gmail.com" target="_blank">khunsanaung.gis@gmail.com</a>><br> <b><span style="font-weight:bold">To:</span></b> Brent Wood <<a href="mailto:pcreso@pcreso.com" target="_blank">pcreso@pcreso.com</a>> <br><b><span style="font-weight:bold">Cc:</span></b> "<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>" <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>> <br> <b><span style="font-weight:bold">Sent:</span></b> Tuesday, February 3, 2015 9:08 PM<br> <b><span style="font-weight:bold">Subject:</span></b> Re: [postgis-users] Convert from Lat/Long point to postGIS geometry<br> </font> </div><div><div class="h5"> <div><br><div><div><div dir="ltr"><div style="color:rgb(0,0,255)">Hi Brent Wood,</div><div style="color:rgb(0,0,255)"><br clear="none"></div><div style="color:rgb(0,0,255)">Many thanks, it works.</div><div><font face="verdana, sans-serif" color="#000000">UPDATE public.town SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);</font><br clear="none"></div><div><font color="#0000ff"><br clear="none"></font></div><div><font color="#0000ff">I am using postGIS to store the data and using GeoServer for publishing the data to maps.</font></div><div><font color="#0000ff"><br clear="none"></font></div><div><font color="#0000ff">I'm thinking to use the GeoExplorer (from OpenGeo Suite) for digitizing and collecting the location information.</font></div><div><font color="#0000ff">When using GeoExplorer, the geometry information is automatically stored to the </font><font face="verdana, sans-serif" color="#000000"><b>geom</b></font><font color="#0000ff"> field of the table and the use have to fill all the attribute again.</font></div><div><font color="#0000ff"><br clear="none"></font></div><div><font color="#0000ff">But I already have the full list in a postGIS table.</font></div><div><font color="#0000ff">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.</font></div><div><font color="#0000ff"><br clear="none"></font></div><div><font color="#0000ff">Many thanks for any idea.</font></div><div><font color="#0000ff"><br clear="none"></font></div><div><font color="#0000ff">Best regards</font></div><div><font color="#0000ff"><br clear="none"></font></div><div style="color:rgb(0,0,255)"><br clear="none"></div></div><div><br clear="none"><div><br><br></div><div><div>On Tue, Feb 3, 2015 at 10:50 AM, Brent Wood <span dir="ltr"><<a rel="nofollow" shape="rect" href="mailto:pcreso@pcreso.com" target="_blank">pcreso@pcreso.com</a>></span> wrote:<br clear="none"><blockquote style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div style="color:#000;background-color:#fff;font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px"><div dir="ltr"><span>Hi.</span></div><div dir="ltr"><span><br clear="none"></span></div><div dir="ltr"><span>Try something like:<br clear="none"></span></div><div dir="ltr"><span><br clear="none"></span></div><div dir="ltr"><span>update <table> set <geometry column> = ST_SetSRID(ST_MakePoint(Longitude, Latitude),4326);</span></div><div><br clear="none"></div><div dir="ltr">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....</div><div dir="ltr"><br clear="none"></div><div dir="ltr">What mapping/GIS program are you using?<br clear="none"></div><div dir="ltr"><br clear="none"></div><div dir="ltr">Cheers,</div><div dir="ltr"><br clear="none"></div><div dir="ltr"> Brent Wood<br clear="none"></div><div><br clear="none"> </div><div style="font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px"> <div style="font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px"> <div dir="ltr"> <hr size="1"> <font face="Arial"> <b><span style="font-weight:bold">From:</span></b> KhunSanAung <<a rel="nofollow" shape="rect" href="mailto:khunsanaung.gis@gmail.com" target="_blank">khunsanaung.gis@gmail.com</a>><br clear="none"> <b><span style="font-weight:bold">To:</span></b> <a rel="nofollow" shape="rect" href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <br clear="none"> <b><span style="font-weight:bold">Sent:</span></b> Tuesday, February 3, 2015 5:11 PM<br clear="none"> <b><span style="font-weight:bold">Subject:</span></b> [postgis-users] Convert from Lat/Long point to postGIS geometry<br clear="none"> </font> </div> <div><div><div><br clear="none"><div><div dir="ltr"><div style="color:rgb(0,0,255)">Hi All,</div><div style="color:rgb(0,0,255)"><br clear="none"></div><div style="color:rgb(0,0,255)">I have one table (Town info) in postgres without Geometry field.</div><div style="color:rgb(0,0,255)">I have Latitude and Longitude information for those points data separately (collecting & filling).<br clear="none"></div><div style="color:rgb(0,0,255)"><br clear="none"></div><div style="color:rgb(0,0,255)">I created the postGIS extension and add the Geometry field in the above postgres table.</div><div style="color:rgb(0,0,255)">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. </div><div style="color:rgb(0,0,255)"><br clear="none"></div><div style="color:rgb(0,0,255)">How can I convert the Latitude/Longitude value into postGIS geometry value?</div><div style="color:rgb(0,0,255)"><br clear="none"></div><div style="color:rgb(0,0,255)">Thank you very much in advance.</div><div style="color:rgb(0,0,255)"><br clear="none"></div>-- <br clear="none"><div><div dir="ltr"><span style="color:rgb(0,0,153)">Have a nice day!</span><br clear="none">--<br clear="none"><div style="line-height:115%"><b><span style="font-size:9pt;line-height:115%;color:rgb(127,127,127)" lang="EN-GB">Mr. Khun San Aung</span></b></div><u><span style="font-size:9pt;line-height:115%;color:blue" lang="EN-GB"><a rel="nofollow" shape="rect" href="http://geoportal.icimod.org/" target="_blank"></a></span></u><u><span style="font-size:9pt;line-height:115%;color:rgb(127,127,127)" lang="EN-GB"></span></u></div></div>
</div></div><br clear="none"></div></div>_______________________________________________<br clear="none">postgis-users mailing list<br clear="none"><a rel="nofollow" shape="rect" href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br clear="none"><a rel="nofollow" shape="rect" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br clear="none"><br clear="none"></div> </div> </div> </div></div></blockquote></div></div><br clear="none"><br clear="all"><div><br clear="none"></div>-- <br clear="none"><div><div dir="ltr"><span style="color:rgb(0,0,153)">Have a nice day!</span><br clear="none">--<br clear="none"><div style="line-height:115%"><b><span style="font-size:9.0pt;line-height:115%;color:#7f7f7f" lang="EN-GB">Mr. Khun San Aung</span></b></div><u><span style="font-size:9.0pt;line-height:115%;color:blue" lang="EN-GB"><a rel="nofollow" shape="rect" href="http://geoportal.icimod.org/" target="_blank"></a></span></u><u><span style="font-size:9.0pt;line-height:115%;color:#7f7f7f" lang="EN-GB"></span></u></div></div>
</div></div></div><br><br></div> </div></div></div> </div> </div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature"><div dir="ltr"><span style="color:rgb(0,0,153)">Have a nice day!</span><br>--<br><p style="line-height:115%"><b><span style="font-size:9.0pt;line-height:115%;color:#7f7f7f" lang="EN-GB">Mr. Khun San Aung</span></b></p><u><span style="font-size:9.0pt;line-height:115%;color:blue" lang="EN-GB"><a href="http://geoportal.icimod.org/" target="_blank"></a></span></u><u><span style="font-size:9.0pt;line-height:115%;color:#7f7f7f" lang="EN-GB"></span></u></div></div>
</div>