[postgis-users] Latitude and Longitude format in PostGIS

[Brent Wood]

--- 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
>