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?

Improve this question
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 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 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 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 at 12:40

1 Answer 1

Reset to default
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" <>''
Improve this answer

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.