0

I am trying to get the points those are within the shapefile. I have tried the Spatial functions and it's showing an error:

3033 - Binary geometry function st_contains given two geometries of different srids: 4326 and 0, which should have been identical.

My query is

SELECT 
lightning.lightning_id, lightning.latitude, lightning.longitude,
lightning.flash_type, 
DATE_FORMAT(lightning.lightning_time, '%D %b %Y %T %p') as lightning_time 
FROM lightning, districts_s 
WHERE 
ST_CONTAINS(districts_s.SHAPE, Point(lightning.longitude, lightning.latitude)) 
AND lightning.height > 50

To rectify the error I got a solution from here and this link but no result, as this also showing an error:

ST_Transform does not exist

update 1

ERROR ON ST_SetSRID enter image description here

2
  • The Point function is not setting an SRID. You can use ST_SetSRID(Point(lon,lat),4326)
    – Vince
    Jan 23 '19 at 13:07
  • Tried the same but got an error called ST_SetSRID does not exist. Check my updated question
    – Saroj
    Jan 23 '19 at 13:30
0

Have you tried this, on both layer:

UPDATE districts_s SET SHAPE = ST_SetSRID(SHAPE, 4326)

But I guess the problem is with lightning, you should save lightning.latitude and lightning.latitude to a geometry (point) column.

5
  • Yes I tried to update the same but it shows me an error called "mydb.ST_SetSRID does not exist"
    – Saroj
    Jan 23 '19 at 11:44
  • Maybe try ST_SRID, or UPDATE Table SET SpatialColumn = ST_GeomFromText(ST_AsText(SpatialColumn), 4326); But I still think that the problem comes from the lightning, what should be in a spatial column as well.
    – poetyi
    Jan 23 '19 at 11:57
  • You mean to say my lat,long should be in a point geometry column?
    – Saroj
    Jan 23 '19 at 12:13
  • Right solution, wrong operand. The table data is fine, but the geometry calculated on the fly doesn't have an SRID.
    – Vince
    Jan 23 '19 at 13:09
  • I checked my SRID of that geometry column using ST_SRID and it's SRID is 4326
    – Saroj
    Jan 23 '19 at 13:58

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.