2

I have a Geoserver WMS layer that, when displayed with the Open Layers preview, doesn't draw a specific polygon when the scale is below 1:2M.

Polygon being displayed as intended

Polygon not being drawn

The layer is a SQL view with the following query:

SELECT * FROM osm.adminareas WHERE fclass='admin_level4' AND ST_Intersects(geom,(SELECT geom FROM osm.adminareas WHERE fclass='national' AND int_name = 'Germany'))
4

Thats because your query doesnt include an order by clause. So it is really a matter of luck to show up as sometimes falls behind the large polygon that overlaps it and some other not. Well , this is a guess but I am pretty sure that this is your problem.

so make your query :

SELECT * FROM osm.adminareas WHERE fclass='admin_level4' AND ST_Intersects(geom,(SELECT geom FROM osm.adminareas WHERE fclass='national' AND int_name = 'Germany')) ORDER BY osm_id ASC

or

SELECT * FROM osm.adminareas WHERE fclass='admin_level4' AND ST_Intersects(geom,(SELECT geom FROM osm.adminareas WHERE fclass='national' AND int_name = 'Germany')) ORDER BY osm_id DESC

UPDATE osm_id is just an idea so we can be sure that this is your problem. Using osm_id for ordering might cause you to hide some other polygons behind larger polygons. It really depends on the nature of your data. Best solution would be to have a field with name "ordering" for example.

  • While the problem is correct, the solution is wrong. ORDER BY osm_id is obviously a bad idea. – pLumo Jul 18 '17 at 13:11
  • Why would it be a bad idea? – cdsa Jul 18 '17 at 13:13
  • Because osm_id is a random id. While it will work for a specific case, it will be bad for the next one. – pLumo Jul 18 '17 at 13:15
  • That is a good point. Do you have any other idea? – cdsa Jul 18 '17 at 13:20
  • see my answer ;-) – pLumo Jul 18 '17 at 13:20
0

When the ordering is not explicitly given, PostgreSQL returns in a random order (the order that is fastest to return).

As Ost-Friesland is part of Niedersachsen, Ost-Friesland will sometimes be below and thus hidden from Niedersachsen.

The best solution would be to fix the column fclass (admin_level) as it is obviously wrong. Ostfriesland should have admin_level5.

Then you can use:

ORDER by fclass DESC

From the data description:

admin_level4: Usually a border of the first level below national

Ost-Friesland is not a Bundesland afaik.


Instead of fixing this, you could use the official EU NUTS dataset.

  • Unfortunately this is OSM data, so I cant really restructure it without insane amounts of effort as this is quite a lot of data. And on top of that adminlevels have meaning associated with them. geofabrik.de/data/geofabrik-osm-gis-standard-0.6.pdf – cdsa Jul 18 '17 at 13:19
  • I would recommend using official EU NUTS dataset instead of osm --> ec.europa.eu/eurostat/de/web/gisco/geodata/reference-data/… – pLumo Jul 18 '17 at 13:23
  • And yes, adminlevels have a meaning, but Ost-Friesland is still wrong then. (admin_level_4 = Usually a border of the first level below national). – pLumo Jul 18 '17 at 13:36
  • I know its really weid, but I cant really chose the data I work with as I am using this at work. And I cant really manually adjust 402064 rows of data. The only thing I can probably do is to add a new column and write a script to order the data... – cdsa Jul 18 '17 at 13:42

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.