2

I'm new to QGIS and I'm using PostGreSQL and QGIS DB Manager to get information from the polygon_osm tables.

I'm currently working on the _polygon table and I'd like to get the coordinates for each point in the polygons.

Here is the problem I am facing:

I am trying to figure out what the "way" field is. When I display the whole table it says "POLYGON" and when I display a specific entry I have very long series of numbers and letters (0-9, A-F) like "0103000020110F00000100000051010000713D0AD7BBDCB..."

and so on (fewer zeros on the right). I'm looking for a way to get a human-readable field, by which I mean coordinates of the points specifying the vertices of the polygon, or id linked to another table (I'm not sure what is stored in this field). The field type is geometry(Geometry,3857). I have a warning message saying "There is no entry in geometry_columns!", I don't know if it is related.

The geom_to_wkt function seems to be what I'm looking for but I am working on tables with millions of entries so I need to do this with a query in DB Manager and not just in the field calculator of the attribute table.

Improve this question
2
  • 2
    for your question about the geometries is here a related answer and a primary key needs to be present
    – LaughU
    Commented Nov 30, 2017 at 7:09
  • Thanks for your help, the post was helpful, although the gserialized documentation is not very clear to me. I was able to identify patterns in the field, but I'm still not sure how to identify blocks (16 ? 32 ?) and I'm not sure if they correspond to latitude and longitude according to the geocash algorithm.
    – Anne
    Commented Nov 30, 2017 at 10:52

1 Answer 1

Reset to default
3

If you want to get a human-readable coordinate from a PostGIS geom field, you can create a new field and store there the geometry as a well-known text.

Workflow as follows:

  • Open the attribute table of your polygon table and go to the field calculator
  • Create a new field as text with the formel geom_to_wkt( $geometry )
  • This will fill the field with something like this: 'MultiPolygon (((698810.86974609 5749870.81713066, 698614.863...' which is in this example the representation of a multi-polygon with x and y coordinates for each vertex

EDIT:

To do this in the DB manager you need to create a field and update it with the ST_AsText function from PostGIS

the query looks like this:

ALTER TABLE my_schema.my_table
    ADD COLUMN wkt_geom text; -- instead of wkt_geom you can name it whatever you like 

Update   my_schema.my_table
    SET wkt_geom = ST_AsText(geom); -- if the geomtry column is named differently you need to change "geom" as well
Improve this answer
4
  • 1
    Great, that's exactly what I was looking for, thanks ! Any idea as to how to do this with a query in DB manager ?
    – Anne
    Commented Nov 30, 2017 at 12:46
  • @Anne see my edit, hope it helps :)
    – LaughU
    Commented Nov 30, 2017 at 13:35
  • Sorry for the delay. Thanks a lot, it helps a great deal ! I had tried something like that but with the geom_to_wkt function, which did not work. One last thing : as you wrote it in your answer, I get something like (4136278.21 5478121.15, ...) where the comma is misplaced, because it is placed so as to have two digits. This may be an issue when I want to join with table with, say, the nodes table. How can I fix this?
    – Anne
    Commented Nov 30, 2017 at 15:18
  • @Anne Not need to worry. The format is POLYGON( x y , x y , ... you could create another field which only holds one pair of x and y values but to do this and how to, you would need to ask a new question after trying a bit around ;)
    – LaughU
    Commented Nov 30, 2017 at 15:33

Your Answer

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

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