Page MenuHomePhabricator

SQL Error - geometries mismatch
Closed, ResolvedPublic

Description

{"name":"service-template-node","hostname":"kartotherian1","pid":11347,"level":50,"message":"500: internal_error","stack":"Error: Postgis Plugin: ERROR:  Operation on two geometries with different SRIDs
in executeQuery Full sql was: 'SELECT ST_AsBinary(\"way\") AS geom,\"osm_id\" FROM (
SELECT osm_id, way
  FROM planet_osm_polygon
  WHERE
    (
      \"natural\" IN ('water', 'bay')
      OR waterway IS NOT NULL
      OR landuse = 'reservoir'
      OR landuse = 'pond'
    )
    AND
    (
      mb_z(68247.3) >= 14
      OR way_area >= 5000000000 / 2.3^mb_z(68247.3)
    )
    AND way && 'BOX3D(523287.8956403165 5743019.683178433,528485.6135637086 5748217.401101825)'::box3d
UNION ALL
SELECT 0 AS osm_id, way
  FROM water_polygons
  WHERE
    way && 'BOX3D(523287.8956403165 5743019.683178433,528485.6135637086 5748217.401101825)'::box3d
) water'

    at Error (native)","status":500,"type":"internal_error","detail":"Postgis Plugin: ERROR:  Operation on two geometries with different SRIDs
in executeQuery Full sql was: 'SELECT ST_AsBinary(\"way\") AS geom,\"osm_id\" FROM (
SELECT osm_id, way
  FROM planet_osm_polygon
  WHERE
    (
      \"natural\" IN ('water', 'bay')
      OR waterway IS NOT NULL
      OR landuse = 'reservoir'
      OR landuse = 'pond'
    )
    AND
    (
      mb_z(68247.3) >= 14
      OR way_area >= 5000000000 / 2.3^mb_z(68247.3)
    )
    AND way && 'BOX3D(523287.8956403165 5743019.683178433,528485.6135637086 5748217.401101825)'::box3d
UNION ALL
SELECT 0 AS osm_id, way
  FROM water_polygons
  WHERE
    way && 'BOX3D(523287.8956403165 5743019.683178433,528485.6135637086 5748217.401101825)'::box3d
) water'
","levelPath":"error/500","request_id":"01e87e8f1cc5485c9b1b8aef84d71623","msg":"","time":"2015-06-02T20:31:24.821Z","v":0}

Event Timeline

Yurik assigned this task to MaxSem.
Yurik raised the priority of this task from to Unbreak Now!.
Yurik updated the task description. (Show Details)
Yurik added a project: Maps.
Yurik subscribed.
MaxSem set Security to None.
MaxSem moved this task from Backlog to Stalled/Waiting on the Maps-Sprint board.

This is because labsdb1004 has Postgres 9.1 and PostGIS 1.5 which are not compatible with the current Mapbox stack. So either you are not able to import coastlines because latest shp2pgsql uses a SRID not supported by PostGIS 1.5 or, if you've imported with an older shp2pgsql you get this error. I tried using spatial_ref_sys from PostGIS 2.1 and it didn't help, so my only idea left is that newer versions are needed. Whatever comes with Debian Jessie works for us in VMs so an OS update on this machine should do it. Not sure if a DB reimport would be needed afterwards though.

ogr2ogr can convert a SHP file SRID (EPSG)

Have you seen http://gis.stackexchange.com/questions/22150/projecting-openstreetmap-data-in-postgis -- it mentions that you can eassily transform stuff with ST_Transform, e.g.

UPDATE osm.roads SET geom_new = ST_Transform(the_geom), 4269);

Resolved, thanks OSM people and all the hard work by @MaxSem.