Postgis longitude, latitude to geometry postgresql

Question

I'm migrating all information in a PostGIS system, with full information about thousands of sport spaces. For all this spaces I got latitude and longitude values, and PostGis need a geometry value for that, this column is already in my table "Location".

The SRID of that application is 23030.

I've looked for how to calculate a geometry value from longitude and latitude, and I find that:

update location set point=ST_GeomFromText('POINT('|| longitud || ' ' || latitud ||')',23030);
UPDATE georrepositorio.geometria SET point = ST_SetSRID(ST_Point( longitud, latitud),23030);
UPDATE georrepositorio.geometria SET POINT = ST_SetSRID(ST_MakePoint(longitud,latitud),23030);

I always get a string like: "0101000020F6590000894327550B97114104EA99EA599D4E41"

In the web application which I am building, if I mark the point to locate the space, it insert in table "location" a string like: "0101000020F659000000000020DFB115C00000008053244240" which looks similar to the string I got using those functions.

The problem is that i can't locate each space because there are so many, so I need a massive migration, and using those function to calculate geometry columns doesn't work. Because ok, those functions calculate a geom value, but when you query the application doesn´t show them.

Anyone knows how to calculate geomtry from latitude and longitude, please? anything

Mike T Mike T · Accepted Answer · 2013-09-25T00:43:43

First, the geometries are stored as binary, which you see as "0101000020...", which is called well-known binary (WKB). For POINT geometries, you can extract the coordinate ordinals using ST_X(point) for longitude and ST_Y(point) for latitude. You can also use ST_AsText(point) for a human-readable WKT representation.

EPSG:23030 is a projected spatial reference system (SRS), with eastings and northings with units of metres. But if your coordinate data is degrees of latitude and longitude, you need to use a different SRS, such as EPSG:4326. Once stored correctly, the data can be re-projected for the application using ST_Transform(point, 23030).

Postgis longitude, latitude to geometry postgresql

1 Answers