0

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.

3
  • 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. 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? Dec 5 '15 at 20:03
0

AS I understand it, you have two tables. One is an internal PostGIS table (public.equipment_nodes) and the other is an external table which you're in the process of importing. Testing for duplicates is done by spatial overlap, i.e. if a feature in the external table is in an identical location as a feature in the internal table, you want to get the internal table's id.

So:

SELECT 
{id_field}
FROM
public.equipment_nodes a
WHERE
ST_Equals(a.geom, ST_GeogFromText('@Value(_location)')::GEOMETRY)

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.