3

I have a PostGIS database filled with OSM data.

I would like to calculate the surface of impervious cover on a given subfraction of my database.

I have tried a more complex version of this query :

SELECT SUM(way_area)
FROM planet_osm_polygon
WHERE
  natural <> ''
  AND way && st_transform(
    st_setsrid(
      st_makebox2d(
        st_point(2.283459561545372, 51.070047438454615),
        st_point(2.2863346738544466, 51.07292255076369)
      ), 4326
    ), 3857
  )
;

I get this error :

ERROR:  syntax error at or near "<>"

LINE 1: ...um(way_area) from planet_osm_polygon where natural<>'' and w...

Trying to understand why, I tried to query for distinct natural and get a similar error.

Those queries work fine if I replace natural by surface or building. Natural is one of the column of my base and the OSM wiki agrees that it has polygon values, so where does that error come from and how do I access the data in there?

4
  • 3
    On a side note, 3857 is not suitable for area computation, and you will want to double check that there is no overlap between the polygons, else you would have to first compute the union of the polygons.
    – JGH
    Jun 12, 2023 at 13:41
  • Hi, thanks for your input, could you expand it a bit more? What could I use? For now I have had no trouble.
    – DianeS
    Jun 12, 2023 at 13:54
  • 2
    :-) no "trouble" but invalid results. 3857 highly distorts distances and areas as you move away from the equator. You would have to find a suitable local projection that preserve areas (UTM or else)
    – JGH
    Jun 12, 2023 at 13:57
  • Do you know if the distortion is stable, in the sense of all distorted areas will be distorted by the same factor? Because I am computing ratios so if the distortion factor is the same I can happily ignore it, especially as my end goal is to work on subsaharian regions so quite close to the equator. Right now I am testing on France because I have more data there and can check my functions more easily. I may open a new question about all that after checking the site for similar posts.
    – DianeS
    Jun 13, 2023 at 12:40

1 Answer 1

5

natural is a reserved keyword so you can't use it directly as a column name. You must double quote it so PG knows you are referencing the column name.

SELECT ... WHERE "natural" <>''

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.