1

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:

https://www.google.es/maps/place/Centre+C%C3%ADvic+Guinard%C3%B3/@41.416402,2.1701573,17z/data=!3m1!4b1!4m5!3m4!1s0x12a4a2cc158f101f:0x215a1deee2ad8d8e!8m2!3d41.416402!4d2.172346

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

API Info: http://opendata-ajuntament.barcelona.cat/es/api-cataleg

Another API Document: https://www.decodeproject.eu/file/203/download&usg=AOvVaw26is6a2BCF_rQkvMA-iDK6

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)

  • 2
    do you know the projection (or CRS) of your input data? without that there is no way to help – Ian Turton May 18 '18 at 12:20
  • 2
    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. – Vince May 18 '18 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. – Trinue May 18 '18 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! – Trinue May 18 '18 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). – mkennedy May 18 '18 at 17:02
1

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)); – Trinue May 21 '18 at 13:47
1

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

  • 1
    The values are way too large for EPSG::3066 – mkennedy May 18 '18 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. – Trinue May 18 '18 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. – Vince May 19 '18 at 3:02
  • hi @Vince, I added more info of the dataset to the post. Thanks all for helping me! – Trinue May 19 '18 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.