Search for contacts within Geocoded regions/borders

Question

My organisation needs to search for contacts within the regions/borders of England, Scotland, and Wales. This is important as our communications relate to elected representatives within devolved parliaments etc.

As all contacts in the UK have country 'United Kingdom' this is not easy.

Postcodes in the UK do not correspond to the borders between England/Scotland and England/Wales, and overlap significantly in some areas.

Postcode Areas and Postcode Districts (the 'SW' and '1W' in the post code 'SW1W 0NY') also do not correspond with the country borders, so it also isn’t possible to search on these (even using ‘Range’ or ‘Regex’).

Would it be possible to geocode the entire areas of England, Scotland, or Wales around known coordinates, and create smart groups based on these?

These three groups alone would be incredibly helpful, but it would be even better were it possible to import coordinates from Google Maps / OpenStreetMap searches (e.g. https://www.google.co.uk/maps/place/Scotland/@56.367676,-4.9607288,8z/data=!4m5!3m4!1s0x4861e2c403f2a19f:0xe7c1fad809c30714!8m2!3d56.4906712!4d-4.2026458), that way other regions could also be quickly turned into Smart groups.

At present we are searching by postcode and excluding contacts using poorly defined 'radius' searches - so finding a better solution would make a huge difference to our work, and I'm sure to many organisation working in the UK.

Answer 1

We had a similar need and used mapit: https://mapit.mysociety.org/. Every time an address is created/updated we use the mapit API to grab the associated parliamentary constituency, European region etc. It looks like the mapit API can give you England/Wales/Scotland/NI too (though it's trivial to translate from the European Region).

We did similar to @ErichBSchulz and created a custom field that is populated by this API lookup. We then have smart groups running off that.

Happy to share our somewhat hacky extension if it'd help, though you'd need to adapt it to your needs. Caveats are: it only works if you have the full postcode; currently we don't have a way to run it for imports; you'll need to populate your existing database somehow (we have a million-row excel spreadsheet we got from one of the government's open data things).

At one point Veda Consulting were working on a service that did something similar - possibly building on top of their postcode lookup service. Might be worth pinging them in case they have something more robust.

EDIT: published this extension at https://github.com/bhahumanists/bha.politicaldata

Answer 2

This is an oversimplification certainly but with either PostgreSQL or MySQL/MariaDB with their Spatial Extensions installed, you can perform basic geometry operations within your database quite easily. The key operation here is ST_Intersection(). You can pass the operator a set of points and a set of polygons and it will return the common set.

See also this post for more info.

Answer 3

If you are using Google Maps you should take advantage of their functionality, eg use an api call to determine if a point is within the polygon of a map layer you have defined, eg https://developers.google.com/maps/documentation/javascript/examples/poly-containsLocation

Answer 4

This isn't possible out of the box but would be an awesome extension.

Assuming google is nicely geocoding your addresses for you then the minimum steps are:

1. install a database with spatial functions (either postgres+postgis or recent MySql (looks like >= 5.6 ought to do it)
2. populate a table with geometry
3. create a custom field (per contact probably best, rather than per address)
4. write a function that return the desired polygon ID for a point
5. write a script that loops over the null values and attempts to fill them in using your function (you will inevitably have need to do this from time to time in the future so best up front - be warned this could be a slow operation so some kind of batching approach worthwild)
6. create a smart groups based on your custom field
7. write a post update hook that captures the address changes and calls your function to update the custom field
8. crack open a cider, put feet up. You deserve it.

As I said it would be an awesome extension. Ideally it would cope with multiple layers. In order for your batch operation to run nicely it would be worth using null for "unknown" and some other value to represent "out of bounds".

Answer 5

JoeMcl recommended that you take a look at this https://dc2014.civicrm.org/got-data-now-what-creating-effective-data-strategy-using-civicrm

Answer 6

Via SE Kurund said:

Web Access developed an extension, that displayed the constituents on the map and you can use turf cutting functionality to create walklist or adding contacts to a group. There were several filter for map display. It was an experiemental early angular interface.

Am waiting on link to code. will add when it comes on SE