0

I have this query where I'm trying to two join tables with st_intersects. There will be different geometry types in the workarea column but the point_geom will always be a point.

with cte1 as (
          select t.number, 
             upper(t.workstreetname) as workstreetname, 
                     upper(t.workcounty) as workcounty, 
                     upper(t.workplace) as workplace, 
                     upper(t.workarea) as workarea
          from schema.ticket t 
              )
cte2 as (select distinct number, workstreetname, workcounty, workplace, workarea from cte1)
select c.number,
       c.workarea as workarea,
       a.geom as point_geom,
from cte2 c
join schema.addresses a on st_intersects(a.geom,c.workarea);

But I am confusing it somewhere because I get this error:

ERROR:  function st_intersects(geometry, text) is not unique
LINE 27: join schema.addresses a on st_intersects(a.geom,c.wo...
                                    ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
********** Error **********

ERROR: function st_intersects(geometry, text) is not unique
SQL state: 42725
Hint: Could not choose a best candidate function. You might need to add explicit type casts.
Character: 1246

Any suggestions on where I am going wrong on this?

  • is workarea a geometry? looks like c.workarea is text – ziggy Apr 10 '18 at 12:48
  • yes, there will be multiple types of geom in that column. – Matt Apr 10 '18 at 12:49
  • 2
    upper(t.workarea) as workarea this is text, also the error tells you the second input for the st_intersects function is text.. – ziggy Apr 10 '18 at 12:51
  • you are right @ziggy – Matt Apr 10 '18 at 12:51
  • 1
    @ziggy write an answer from your comment...let's follow the efforts to have less 'unanswered/answered in comments' questions on this board (I need to improve on that myself) ,) – geozelot Apr 10 '18 at 13:31
1

In your first CTE, upper(t.workarea) as workarea is a text field. later when calling st_intersects(a.geom,c.workarea), that same workarea text field is used as the second input for the st_intersects function which gives the insightful error. Make sure both inputs are geometries

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.