[postgis-users] Latitude and Longitude format in PostGIS

[Giuseppe Molinario]

Brent,

Thank you - We are using the Sql code you gave us and it is working good.
We've managed to automate the Lat/long coordinates from an entering gprmc
format email right to our Web-GIS service.


Our problem now, that we are dealing with, is the creation of a line from
the inserted points. Do we really have to go back and use the linestring
from text function? converting again the lat/long coordinates? Or do can we
just use a Postgis function that "Connects the dots" from the postgis format
(have a geom column) points that are already in the DB?

Thanks again for th help guys!

Giuseppe




On 4/12/07, Brent Wood <pcreso at pcreso.com> wrote:
>
>
> --- Giuseppe Molinario <g.molinario at gmail.com> wrote:
>
> You can use (I think) the proj binary to convert the dddmm.mmm to dd.dddd,
> but
> it may be easier to use an SQL with the data in Postgis tables as numeric
> data.
>
> The reformatting of ddmm.mmmm to dd.ddddd is not technically reprojecting.
>
> The following is one way to do this, by inserting your ddmmm.mmmm values,
> then
> generating a point geometry from these with an update SQL.
>
>
> ie, along the lines of:
>
> # create your table...
> create table coords
>   (id   serial primary key,
>    x    decimal(10,5),
>    y    decinal(10,5))
>
> load your XY data...
> insert into coords values (default, 16778.8888, 4234.6667);
>
> # add a geometry column to the table
> select addgeometrycolumn('','coords','the_geom',4326,'POINT',2);
>
> #populate the geometry column
> update coords
> set
> the_geom=geomfromtext('POINT('||floor(x/100)+((x/100-floor(x/100))/0.60)||'
> '||floor(y/100)+((y/100-floor(y/100))/0.60)||')',4326);
>
> select id, x, y, astext(the_geom) from coords;
> id |      x      |     y      |                  astext
> ----+-------------+------------+------------------------------------------
>   3 | 16778.88880 | 4234.66670 | POINT(168.314813333333 42.5777783333333)
> (1 row)
>
>
> note that floor as used above will not work with negative coords, but you
> can
> still use a similar approach using strings, or ceil/int & a where clause
> for
> negative coords....
>
> If you are comfortable writing a postgres function to do this conversion,
> that
> is also possible.
>
>
> Cheers,
>
>   Brent Wood
>
> > Hi Mark,
> >
> > Yes, that helps. I'll see if i missed the proj4 installation along the
> way,
> > or if, more probably i am simply feeding PostGIS lat/long data that is
> not
> > in decimal degrees.
> >
> > I'll let you know if it doesn't work.
> >
> > Thanks,
> >
> > Giuseppe
> >
> >
> >
> > On 4/11/07, Mark Cave-Ayland <mark.cave-ayland at ilande.co.uk> wrote:
> > >
> > > On Wed, 2007-04-11 at 13:58 +0200, Giuseppe Molinario wrote:
> > > > Hi everyone,
> > >
> > > Hi Guiseppe,
> > >
> > > > I have a question regarding the creation in PostGIS of a table that
> > > > contains information coming from a NMEA GPRMC text string. I get
> > > > Lat/long coordinates which are then put into their respective
> columns.
> > > > A geometry field is then created using the SRID # 4326 (WGS84 Datum
> -
> > > > Geographic).
> > > >
> > > > 1. What format of Lat/long coordinates does PostGIS accept? GPRMC
> has
> > > > the coordinate in this format: ddmm.mmmmm.
> > >
> > > PostGIS accepts coordinates in decimal degrees.
> > >
> > > > 2. How do I project the points into WGS84UTM33N? My final goal is to
> > > > overlay these points as a PostGIS layer on a webmap (Mapserver
> based)
> > > > over other data I have which is in that projection.
> > >
> > > The transform() function should be able to do what you need here if
> you
> > > use a version of PostGIS that has been compiled with the PROJ.4
> > > reprojection library.
> > >
> > > > 3. Do you know if Mapserver will re-project on the fly in my case?
> > > > ever? or do I have to take care of this in PostGIS?
> > >
> > > Yes. Mapserver can also reproject layers on-the-fly using PROJ.4.
> There
> > > are plenty of examples on the Mapserver website at
> > > http://mapserver.gis.umn.edu.
> > >
> > >
> > > HTH,
> > >
> > > Mark.
> > >
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at postgis.refractions.net
> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070412/ddae7b01/attachment.html>