1

I want to query which ward contains the most cycle stations (a cycle station is a 3D geometry i.e. polyhedral) - the wards are 2D polygons - below is what I have worked out so far - The error I get when I run the query is also shown:

 SELECT COUNT(ST_Contains(a.location, b.location)) 
   FROM wards a, cycle_stations b;

 ERROR:  Unknown geometry type: 13 - PolyhedralSurface

However I am aware that I am trying to query both 3d and 2d coordinates - which is where I am lost:

I guess what im wondering is - whether it is possible to do a within/contains query for 2d/3d geometries - if so, what would it be.

  • 1
    ST_Contains does not support 3D geometries. not 100% sure what it does exactly when called with POLYHEDRALs; in theory, if it doesn't prevent it's use in the first place, it should drop the Z index. doing so however, will likely produce invalid 2D POLYGONs, resulting in unreliable comparisons. you can use ST_Force2D and run ST_Contains with the results (make sure the results are valid). or, if true containment is not required, use ST_3DIntersects (can cycle stations be on a boundary between 2 wards, and if, how would you like to handle that?) – ThingumaBob Apr 22 '18 at 9:49
  • So do you have an idea of what the query could be? – hazard335 Apr 22 '18 at 19:57
  • 1
    I would think that you can happily use 2D geometries for checking cycle stations within a ward? 3D is more relevant if you are interested in line of sight or similar. if you do have a use case where 3D containment of a cycle station is relevent, I would love to see it. – John Powell Apr 22 '18 at 21:25
  • There is ST_3DIntersects and also ST_DFullyWithin. I realize this isn't exactly the same as contains, but you might be able to get close by using Intersects and a distance of 0 for FullyWithin. – John Powell Apr 23 '18 at 9:01
  • Just out of curiosity, following @JohnPowellakaBarça...why indeed would you have cycle stations in 3D??? – ThingumaBob Apr 23 '18 at 10:22
3

One approach to doing this, would be to note that if one 3D object is fully contained within another, then ST_Volume should equal ST_Volume (ST_3DIntersection(a.geom, g.geom)).

For example, with two cubes:

WITH 
   cube (geom) AS (
     SELECT 
       ST_Extrude(
           ST_Expand(
               ST_Makepoint(8, 8), 
           5, 5), 
       0, 0, 5)) , 
   cube1 (geom) AS (
        SELECT
            ST_Extrude(
                 ST_Expand(
                    ST_MakePoint(0,0),
                 10, 10),
              0, 0, 20)
           )
SELECT 
     ST_Volume(c.geom) as cube_volume, 
     ST_Volume(ST_3Dintersection(c.geom, c1.geom))  AS volume_intersection
 FROM cube c, cube1 c1;                

returns:

cube_volume         | 500
volume_intersection | 245

As the first cube has an offset centre point. So, if you tested for volume = 3d intersection volume you would have a 3D contains function -- though you might want to exclude geometries that touch.

However, from you question, it sounds as though the 3D component of the cycle stations is not relevant to the query, if you are just wanting to get a count of cycle stations in each ward. Further, to make the above query work, you would have to extrude your wards in the z direction, which is almost certainly not what you want.

What you can do is use ST_Dump which will convert PolyhedralSurfaceZ into individual polygons or possibly even better, use, ST_Extent, as this will give you the 2D ground level polygon, rather than multiple polygons that you will get from ST_Dump.

For example, after creating a 20 x 20 x 20 cube, taking the 2D extent, gives you:

SELECT  
   ST_AStext(
     ST_Extent(
         ST_Force3d(
              ST_Extrude(
                  ST_Expand(
                     ST_MakePoint(0,0),
                  10, 10),
              0, 0, 20)
         )
      )
   );  

returns:

POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10))

whereas with ST_3DExtent, you get,

POLYHEDRALSURFACE Z (((-10 -10 0,-10 10 0,10 10 0,10 -10 0,-10 -10 0)),((-10 -10 10,-10 10 10,10 10 10,10 -10 10,-10 -10 10)),((-10 -10 0,-10 10 0,-10 10 10,-10 -10 10,-10 -10 0)),((10 -10 0,10 10 0,10 10 10,10 -10 10,10 -10 0)) ,((-10 -10 0,10 -10 0,10 -10 10,-10 -10 10,-10 -10 0)),((-10 10 0,10 10 0,10 10 10,-10 10 10,-10 10 0)));

You might want to create a new table with the 2D extents, using ST_Extent, so you can put a spatial index on it, after which you can run:

SELECT 
    a.name, 
    COUNT(b.location) 
 FROM 
     wards a, cycle_stations b
WHERE ST_Contains(a.location, b.location);

If you don't wish to do it this way, you can probably get close using ST_3DIntersects and/or ST_DFullyWithin with a distance of 0.

  • hmm...ST_Extend is tricky here, because a bbox can be contained while the actual geometry is not, or vice versa. ST_Dump will return each face as a polygon, possibly making them invalid in 2D. I´d rather go for ST_Collect(ST_Force2D(ST_DumpPoints(<POLYHEDRALSURFACEZ>))) (<- pseudocode!) and check for containment with the MULTIPOINTs – ThingumaBob Apr 23 '18 at 10:46
  • @ThigumaBob. ST_Dump is totally valid in this context: what you get is polygons, with the Z value included. If you look at the question that the OP is trying to answer, how many cycle stations are in a ward, I think you will agree that the 2D extent of the polyhedral surface return by ST_Extent will do the job, unless these a very space age bike stations in shape. I have just done a similar exercise for trees and line of sight, where the 3D extents do matter, so I have thought about this kind of problem a lot. – John Powell Apr 23 '18 at 11:08
  • yes yes, what I didn´t know (and find amusing) is that ST_Contains works with POLYGON Z (as, e.g. with the 3D extend above, ST_Force2D will create invalid geometries); this is similar, albeit simpler than getting MULTIPOINTs and here comes my 'I must admid...'. having a corner of a bbox being just outside a ward, however, is not even ridicuously off. and simply annoying. thus my comment above partially stands. – ThingumaBob Apr 23 '18 at 12:03
  • @ThingumaBob. Sure, your comment above stands. I have edited the question with a way to approximate a 3D contains function, using ST_Volume and ST_3DIntersection. However, as this would involve extruding the ward boundaies vertically in order to work, it would be slow and pointless, imho. The OP has asked about ST_3DContains because the data is a Polyhedral surface, but based on the question, I am convinved 2D will work and be a lot faster -- 3D intersections can be very slow on complex objects, not surprisingly. ST_Extent would seem fine for all but the most insane edge cases :-) – John Powell Apr 23 '18 at 12:15
  • @ThingumaBob. Agreed. I am really enjoying working with the 3D functions, but there are quite a few surprises in store, I have found, which make sense once you think about it. – John Powell Apr 23 '18 at 12:17

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.