2

I am making a map using data stored in PostGIS. I am serving multiple layers in the map (POIs, Cartographic, Roads etc.) and when I label these layers I have repeating labels, this is because some features are represented as both Polygons and Points in my data (present in both the cartographic layer and the POI layer).

I tried to fix the issue of duplicate features by running a Join in PostGIS based on the names and then removing the features from one table, using this query:

select c.name AS cartographic, subtype AS carto_type, type_strin AS pois_type, p.name AS pois 
from cartographic c , isl_pois_priority p
where (lower(c.name) LIKE lower(p.name)) AND c.a1 > 0

At first I though this would work but then I realized that the names in both the tables are not exact matches, they are mostly the same but there are some differences, as shown here: enter image description here

The point layer contains spaces( ) while the polygon layer has hyphens (-).

How can I account for differences such as this when I look for duplicate features?

1 Answer 1

3

You can use fuzzystrmatch module of PostgreSQL. It provides several functions to determine similarities and distance between strings.

I think something similar to this will work for you.

select c.name AS cartographic, subtype AS carto_type, type_strin AS pois_type, p.name AS pois 
from cartographic c , isl_pois_priority p
where (metaphone(c.name, 20) LIKE metaphone(p.name, 20)) AND c.a1 > 0
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.