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  
  ST_Within(shape::GEOMETRY, ST_GeogFromText('@Value(_location)')::GEOMETRY)

 count(*) > 0 AS node_exists

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.

  • If I understand this correctly, you're ultimately trying to insert new rows into an existing table, but are worried that you'll have conflicts? This should be managed by your database schema: are you using a primary key? You can tell Postgres what to do ON CONFLICT with a unique constraint. – Richard Law Dec 4 '15 at 22:37
  • What I am trying to do is figure out first if the sensors are already in the database. They need to be inserted first to generate a primary key so before i do that i want to see if it is. And if it is already in the database i want to grab the primary key and attributes of the data already in the database – Kyle K Dec 5 '15 at 5:21
  • I'm sorry I still don't follow. Can you perhaps edit in information about your schema into the question. Maybe a small SQL dump on Dropbox? – Richard Law Dec 5 '15 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.