1

I'm kind of new on PostGIS. I have the coordinates (UTM) in a table, and I need to update the geometry field based on that coordinates.

enter image description here

The SRID in the table is 32717. I tried this sentence

UPDATE public.uems
    SET coordenada_uem=ST_Multi(ST_SetSRID ((ST_MakePoint(172332.96000 , 68435.29000)), 32717))
    WHERE gid=78;`

And the result is this when I've tried to see the geometry

enter image description here

The updated point is really far away from the real ubication, and even the country (Ecuador).

How can I fix that?

Improve this question
5
  • Those Y values are all over the place, I suggest you check your X,Y values before trying to fix your SQL.
    – Michael Stimson
    Mar 17, 2021 at 23:43
  • Further to the observation from @MichaelStimson your Y values differ by at least 3 orders of magnitude- even on the limited data you show. If they are in meters then they are going to range over somewhere between 100-1,000 km. At least the X values have the magnitude. However, they seem to be in 2 clusters that would be at least 500 km apart. While you are checking the source data, try to confirm that they are all in the same UTM zone.
    – Leigh Bettenay
    Mar 18, 2021 at 0:19
  • Also if you're using the geometry viewer of PgAdmin, I believe you need to convert a geometry to SRID 4326 (or cast to ::geography) before it will display over a basemap like you might be expecting.
    – alphabetasoup
    Mar 18, 2021 at 3:40
  • I check the X and Y coordinates, I used Google Maps and the coordinates are this 0°37'06.0"N 77°56'36.7"W. Then I transform it in another page (mundivideo.com/coordenadas.htm) And the results are this for X: 172332.96 Y: 68435.29 I don't know if this page it's wrong or something, because I made the same process for other ones, and works good.
    – Jeff_Barahona
    Mar 18, 2021 at 4:01
  • You could get PostGIS to do the conversion for you using st_transform - so you enter the lon/lat coordinates directly into your SQL
    – Ian Turton ♦
    Mar 18, 2021 at 8:41

1 Answer 1

Reset to default
2

I'm putting this in as answer rather than another comment because I cannot put an image in comments (so far as I can tell), and I think an image might help you. You need to check that your source data are all in the same UTM zone and, if they are not then either load them as latitude-longitude pairs and convert later to UTM, or load each UTM zone separately while identifying each during the load process.

The point you gave us (0°37'06.0"N 77°56'36.7"W) is within UTM zone 18N and has the UTM Zone 18 coordinates you gave us in your comment. However it is only 6.2km east of the zone boundary and in the NE part of the country. Any point lying more that 6km to the west of this point will NOT be in UTM zone 18. Furthermore the maximum northing you could get in this zone boundary while still being in Ecuador is about 101,650N. You have given us several Y values that are far in excess of this and so cannot be UTM Zone 18 coordinates lying within Ecuador.

This picture shows the control point, UTM zone boundaries and the country of Ecuaador

Improve this answer
2
  • 1
    Thanks for that, I don't even notice that Now the problem it's the column has SRID 32717 as default. I have the idea to look for a sentence that only changes that single register Tried with ST_Transform but don't work, it shows the message: Geometry SRID (32618) does not match column SRID (32717) Any advice for that?
    – Jeff_Barahona
    Mar 18, 2021 at 20:45
  • 1
    Perhaps follow the suggestion made by @IanTurton and ask another related question specifically about PostGIS st_transform if you are still having trouble.
    – Leigh Bettenay
    Mar 18, 2021 at 23:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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