<html><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px"><div dir="ltr" id="yui_3_16_0_1_1422934971250_117413"><span id="yui_3_16_0_1_1422934971250_117453">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 id="yui_3_16_0_1_1422934971250_117475" dir="ltr"><span id="yui_3_16_0_1_1422934971250_117453"><br></span></div><div id="yui_3_16_0_1_1422934971250_117476" dir="ltr"><span id="yui_3_16_0_1_1422934971250_117453">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 id="yui_3_16_0_1_1422934971250_125516" dir="ltr"><br><span id="yui_3_16_0_1_1422934971250_117453"></span></div><div id="yui_3_16_0_1_1422934971250_125517" dir="ltr"><span id="yui_3_16_0_1_1422934971250_117453">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 id="yui_3_16_0_1_1422934971250_125518" dir="ltr"><br><span id="yui_3_16_0_1_1422934971250_117453"></span></div><div id="yui_3_16_0_1_1422934971250_125519" dir="ltr"><span id="yui_3_16_0_1_1422934971250_117453">Cheers</span></div><div id="yui_3_16_0_1_1422934971250_125520" dir="ltr"><br><span id="yui_3_16_0_1_1422934971250_117453"></span></div><div id="yui_3_16_0_1_1422934971250_125521" dir="ltr"><span id="yui_3_16_0_1_1422934971250_117453"> Brent</span></div><div id="yui_3_16_0_1_1422934971250_125522" dir="ltr"><br></div><div id="yui_3_16_0_1_1422934971250_125559" dir="ltr"><br></div><div id="yui_3_16_0_1_1422934971250_125560" dir="ltr"><br><span id="yui_3_16_0_1_1422934971250_117453"></span></div><div id="yui_3_16_0_1_1422934971250_125523" dir="ltr"><span id="yui_3_16_0_1_1422934971250_117453">#! /bin/bash<br style="" class=""># script to create database, install postgis, and create:<br style="" class=""># a table with a geometry column & x,y columns<br style="" class=""># a trigger function to update the table geometry column,<br style="" class=""># populating null geometries with a geometry made from coords <br style="" class=""># a trigger invoking the function on update<br style="" class=""># run a couple of inserts to test it works<br style="" class=""># look at the result<br style="" class=""><br style="" class="">dropdb test<br style="" class="">createdb test<br style="" class="">psql -d test -c "create extension postgis;"<br style="" class="">psql -d test -c "create table test_trigger <br style="" class=""> (id serial primary key,<br style="" class=""> x decimal(7,4),<br style="" class=""> y decimal(7,4),<br style="" class=""> geom geometry(point, 4326));"<br style="" class=""><br style="" class="">psql -d test -c "CREATE OR REPLACE Function update_geom() RETURNS TRIGGER AS <br style="" class=""> \$\$<br style="" class=""> BEGIN <br style="" class=""> UPDATE test_trigger SET geom = ST_SetSRID(ST_Makepoint(x,y),4326) where geom isnull;<br style="" class=""> RETURN null;<br style="" class=""> END;<br style="" class=""> \$\$ <br style="" class=""> LANGUAGE 'plpgsql';"<br style="" class=""><br style="" class="">psql -d test -c "CREATE TRIGGER geom_trigger AFTER INSERT ON test_trigger FOR EACH ROW EXECUTE PROCEDURE update_geom();"<br style="" class=""><br style="" class="">psql -d test -c "insert into test_trigger (x, y) values (179.0, -45.0);"<br style="" class="">psql -d test -c "insert into test_trigger (x, y) values (179.5, -45.3);"<br style="" class="">psql -d test -c "select id, x, y, ST_AsText(geom) from test_trigger;"<br style="" class=""><br></span></div><div id="yui_3_16_0_1_1422934971250_125514" dir="ltr"><br></div><div id="yui_3_16_0_1_1422934971250_125587" dir="ltr"><br></div><div id="yui_3_16_0_1_1422934971250_125588" dir="ltr">The result of running this is:</div><div id="yui_3_16_0_1_1422934971250_125602" dir="ltr"><br></div><div id="yui_3_16_0_1_1422934971250_125589" dir="ltr">CREATE EXTENSION<br style="" class="">CREATE TABLE<br style="" class="">CREATE FUNCTION<br style="" class="">CREATE TRIGGER<br style="" class="">INSERT 0 1<br style="" class="">INSERT 0 1<br style="" class=""> id | x | y | st_astext <br style="" class="">----+----------+----------+--------------------<br style="" class=""> 1 | 179.0000 | -45.0000 | POINT(179 -45)<br style="" class=""> 2 | 179.5000 | -45.3000 | POINT(179.5 -45.3)<br style="" class="">(2 rows)<br style="" class=""><br><span id="yui_3_16_0_1_1422934971250_117453"></span></div><div id="yui_3_16_0_1_1422934971250_125581" dir="ltr"><span id="yui_3_16_0_1_1422934971250_117453"></span></div><br> <div id="yui_3_16_0_1_1422934971250_117479" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1422934971250_117478" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1422934971250_117477" dir="ltr"> <hr id="yui_3_16_0_1_1422934971250_117502" size="1"> <font id="yui_3_16_0_1_1422934971250_117481" face="Arial" size="2"> <b><span style="font-weight:bold;">From:</span></b> KhunSanAung <khunsanaung.gis@gmail.com><br> <b><span style="font-weight: bold;">To:</span></b> Brent Wood <pcreso@pcreso.com> <br><b><span style="font-weight: bold;">Cc:</span></b> "postgis-users@lists.osgeo.org" <postgis-users@lists.osgeo.org> <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 id="yui_3_16_0_1_1422934971250_117480" class="y_msg_container"><br><div id="yiv7318035546"><div><div dir="ltr"><div class="yiv7318035546gmail_default" style="color:rgb(0,0,255);">Hi Brent Wood,</div><div class="yiv7318035546gmail_default" style="color:rgb(0,0,255);"><br clear="none"></div><div class="yiv7318035546gmail_default" style="color:rgb(0,0,255);">Many thanks, it works.</div><div class="yiv7318035546gmail_default" style=""><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 class="yiv7318035546gmail_default" style=""><font color="#0000ff"><br clear="none"></font></div><div class="yiv7318035546gmail_default" style=""><font color="#0000ff">I am using postGIS to store the data and using GeoServer for publishing the data to maps.</font></div><div class="yiv7318035546gmail_default" style=""><font color="#0000ff"><br clear="none"></font></div><div class="yiv7318035546gmail_default" style=""><font color="#0000ff">I'm thinking to use the GeoExplorer (from OpenGeo Suite) for digitizing and collecting the location information.</font></div><div class="yiv7318035546gmail_default" style=""><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 class="yiv7318035546gmail_default" style=""><font color="#0000ff"><br clear="none"></font></div><div class="yiv7318035546gmail_default" style=""><font color="#0000ff">But I already have the full list in a postGIS table.</font></div><div class="yiv7318035546gmail_default" style=""><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 class="yiv7318035546gmail_default" style=""><font color="#0000ff"><br clear="none"></font></div><div class="yiv7318035546gmail_default" style=""><font color="#0000ff">Many thanks for any idea.</font></div><div class="yiv7318035546gmail_default" style=""><font color="#0000ff"><br clear="none"></font></div><div class="yiv7318035546gmail_default" style=""><font color="#0000ff">Best regards</font></div><div class="yiv7318035546gmail_default" style=""><font color="#0000ff"><br clear="none"></font></div><div class="yiv7318035546gmail_default" style="color:rgb(0,0,255);"><br clear="none"></div></div><div class="yiv7318035546gmail_extra"><br clear="none"><div class="qtdSeparateBR"><br><br></div><div class="yiv7318035546yqt9825652719" id="yiv7318035546yqt70078"><div class="yiv7318035546gmail_quote">On Tue, Feb 3, 2015 at 10:50 AM, Brent Wood <span dir="ltr"><<a rel="nofollow" shape="rect" ymailto="mailto:pcreso@pcreso.com" target="_blank" href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</a>></span> wrote:<br clear="none"><blockquote class="yiv7318035546gmail_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>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" ymailto="mailto:khunsanaung.gis@gmail.com" target="_blank" href="mailto:khunsanaung.gis@gmail.com">khunsanaung.gis@gmail.com</a>><br clear="none"> <b><span style="font-weight:bold;">To:</span></b> <a rel="nofollow" shape="rect" ymailto="mailto:postgis-users@lists.osgeo.org" target="_blank" href="mailto:postgis-users@lists.osgeo.org">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 class="yiv7318035546h5"><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" target="_blank" href="http://geoportal.icimod.org/"></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" ymailto="mailto:postgis-users@lists.osgeo.org" target="_blank" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br clear="none"><a rel="nofollow" shape="rect" target="_blank" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">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 class="yiv7318035546gmail_signature"><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" target="_blank" href="http://geoportal.icimod.org/"></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></body></html>