# PostGIS - Need to convert Integer type X/Y coordinates to decimal lat/lon

I am working on a data warehouse project. I need to load a XML file information to our data warehouse. This file contains geographic information:

``````<geocodificacio x="31008099" y="85478573"/>
``````

Our data warehouse has two columns for lat lon but they are in decimal (float) data type. I need to find a way to convert this integer values to float.

I tried using PostGIS to archieve this but no luck.

Edit: Basically what I need is to obtain decimal values from those integer values. For example:

``````<geocodificacio x="31008099" y="85478573"/>
``````

This coordinates are in WGS84 format and point to Ronda Guinardo in Barcelona:

So I need a way to figure how to convert:

x="31008099" to 2.172491 and y="85478573" to 41.416455

Any ideas?

Edit2: The dataset is this and as you can see on the webpage, the reference system used is ED50:

http://opendata-ajuntament.barcelona.cat/data/en/dataset/agenda-mensual/resource/2469d688-6056-48cb-a043-8cd8587f6890

This last document points to a custom transformation formula from ED50 to WGS84:

Coordinates conversion Custom ED50 to WGS84: ED50(long_iris / 1000 + 400000, lat_iris / 1000 + 4500000)

But it doesn´t work either.

I tried using PostGis ST_Transform to obtain lat lon based on ED50 but no result. I suppose that UTM Zone is 31T (Barcelona)

• do you know the projection (or CRS) of your input data? without that there is no way to help May 18, 2018 at 12:20
• Welcome to GIS SE. As a new user, please take the Tour. It is a mistake to classify this as an integer/float conversion problem. You need to research your input data to discover the units of the X and Y fields. If they are Cartesian values, then you need to discover which coordinate system. Finally, at that point you can try again with PostGIS, and if that doesn't work, edit this question with the details of the attempt. May 18, 2018 at 12:27
• Thanks @Vince! I was not aware of the tour. As I told Turton, they are on WGS84(x,y) format after converting them from ED50. May 18, 2018 at 13:16
• Hi @Ian-Turton ! They are on WGS84(x,y) format after converting them from ED50. Sorry, I am totally noob on GIS. Could you please help me?? Thanks! May 18, 2018 at 13:29
• Where is the data supposed to be? If they are lat/lon, they could be DDMMmmmm or DD.dddddd (d = degree, M = integer part of minutes, m = decimal portion of minutes). May 18, 2018 at 17:02

I do not know PostGIS so I can't give you ST_TRANSFORM specifics. There are plenty of Q&As on the site.

GIS data is usually in a projected coordinate reference system (CRS) or 2D geographic CRS (latitude-longitude). Let's ignore height for now. A projected CRS is based/includes a geographic CRS that defines the earth's size and shape.

The metadata says to divide by 1000 and add 400000 to the x value and 4500000 to the y value. That gives us:

``````431008.099 4585478.573
``````

It also says that the conversion is from ED50 to WGS84. That's quite suspicious because the difference between two geographic CRS (also known as geodetic datums) are not converted like that. They don't have an even offset like that, and the conversion is simply a scaling and translation. I think it converts from integer values to UTM zone 31 North, but we still don't know if the CRS is based on ED50 or WGS84.

Starting values after applying the scaling and translations are:

``````431008.099  4585478.573
``````

If I use EPSG:23031 (ED50 / UTM Zone 31N), I get:

`````` 41.4171766078488    2.174473343921858
``````

If I use EPSG:32631 (WGS 84 / UTM Zone 31N), I get:

`````` 41.41790111547008   2.174426556295089
``````

If I convert from ED50 to WGS84 with a 7 parameter transformation, EPSG:1632:

`````` 41.41605043199022   2.173318938341557
``````

So you can see that we're getting 3 different answers. Maybe they're close enough that it doesn't matter but it's still something to check out.

• Thanks @mkennedy! You were right! I ended writing this line on pgadmin and it worked! SELECT ST_AsText(ST_Transform(ST_PointFromText('POINT(431008.099 4585478.573)',23031), 4258)); May 21, 2018 at 13:47

I believe that coordinate system info is found here: https://epsg.io/3066

If so, the SRID of ED50 is 3066

The SRID of WGS84, which you want to transform, is 4326.

``````select
ST_AsText(
ST_Transform(
ST_SetSRID(ST_MakePoint('31008099', '85478573'), 3066)
, 4326)
)
as geom
``````

If you're working on updating two columns of data to WGS84, you would first need to build geometry in WGS84, then convert the LON/LAT values to seperate columns:

``````select

ST_X(ST_Transform(data.geom, 4326)) as lon
, ST_Y(ST_Transform(data.geom, 4326)) as lat

from (
select ST_SetSRID(ST_MakePoint('31008099', '85478573'), 3066)
as geom
) as data
``````

Give those a shot and see if it works for your data...

• The values are way too large for EPSG::3066 May 18, 2018 at 17:02
• The values are already at WGS84. What I need is to obtain lat lon on decimal data type. For example, turn 31008099 to 2.456354. May 18, 2018 at 18:48
• @Trinue WGS84 is both a geographic coordinate system (GCS) and the basis for a projected coordinate system (PCS), so asserting that the coordinate values are WGS84 is useless without knowing which of the infinite number of PCS parameter combinations is in use. You must, as Ian and Melita indicated, contact your data source to determine the correct spatial reference, or conversion to known units will not be possible. May 19, 2018 at 3:02
• hi @Vince, I added more info of the dataset to the post. Thanks all for helping me! May 19, 2018 at 6:43