I am doing a spatial query to see if water sensors exist or not in a database. If so I want to return the unique identifier # that exists on the ones in the database so I can match it to the ones that are going to be added to the database. This # is populated when it is inserted so want to make sure i dont have one sensor with two different unique identifier numbers.
So far here is my code for trying to do the spatial search but not sure how to return the unique identifier for ones that already exist.
WITH nodes_existing AS ( SELECT * FROM public.equipment_nodes WHERE ST_Within(shape::GEOMETRY, ST_GeogFromText('@Value(_location)')::GEOMETRY) ) SELECT count(*) > 0 AS node_exists FROM nodes_existing, If any one can help me pull the records if the node exists that would be awesome. I am not sure where to go next.
Also _location is geometry extracted from the incoming files with the nodes that I am trying to see if they exist or not. I do know this part works so far.
Thank you for any help
أكثر...
So far here is my code for trying to do the spatial search but not sure how to return the unique identifier for ones that already exist.
WITH nodes_existing AS ( SELECT * FROM public.equipment_nodes WHERE ST_Within(shape::GEOMETRY, ST_GeogFromText('@Value(_location)')::GEOMETRY) ) SELECT count(*) > 0 AS node_exists FROM nodes_existing, If any one can help me pull the records if the node exists that would be awesome. I am not sure where to go next.
Also _location is geometry extracted from the incoming files with the nodes that I am trying to see if they exist or not. I do know this part works so far.
Thank you for any help
أكثر...