1

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

Community
  • 1
Saroj
  • 111
  • 1
  • 5
  • 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

1 Answers1

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.

pnz1337
  • 1,565
  • 1
  • 13
  • 25
  • 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. – pnz1337 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