Answers.Body: <p>We had a similar need and used mapit: <a href="https://mapit.mysociety.org/" rel="nofollow noreferrer">https://mapit.mysociety.org/</a>. 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).</p>
  
  <p>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.</p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>EDIT: published this extension at <a href="https://github.com/bhahumanists/bha.politicaldata" rel="nofollow noreferrer">https://github.com/bhahumanists/bha.politicaldata</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-21T14:27:20.147Z
Answers.Text: Sounds very promising, thanks for outlining your solution. **Would really appreciate taking a look at your extension**. And will defintely contact Veda to see how far they got with this.
Answers.UserDisplayName: Michael Moore
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-21T14:35:21.843Z
Answers.Text: No problem - ping me an email at awest@humanism.org.uk and I'll send it over.
Answers.UserDisplayName: Andrew West
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-21T18:41:14.600Z
Answers.Text: @AndrewWest that sounds similar to what Jon Goldberg did using the Open States API ( http://docs.openstates.org/en/latest/api/ ) for getting district info US geocoded addresses in Civi, and what Joe Murray and crew have done using https://represent.opennorth.ca/api/ for Canadian addresses in Civi. Could you possibly share the "hacky" extension in a public repo as well?
Answers.UserDisplayName: Joe McLaughlin
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-21T18:58:04.893Z
Answers.Text: See https://github.com/JMAConsulting/biz.jmaconsulting.petitionCanada for the extension repo for Canada and https://hq.palantetech.coop/projects/electoral-api/wiki/User_Documentation for the extension repo for the US.
Answers.UserDisplayName: Joe McLaughlin
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-21T19:09:06.130Z
Answers.Text: Have to give credit to Edsel Lopez for his work on the Canada extension, and Joseph Lacey for his work on the US extension, see links above.
Answers.UserDisplayName: Joe McLaughlin
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-23T14:16:38.170Z
Answers.Text: Just published our extension at: https://github.com/bhahumanists/bha.politicaldata. This is the first one I've made public, so please do let me know if I've made any mistakes...
Answers.UserDisplayName: Andrew West
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-06-07T06:28:02.837Z
Answers.Text: @AndrewWest a very belated thank you for publishing your extension!
Answers.UserDisplayName: Joe McLaughlin
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-21T13:39:42.763Z
Answers.LastActivityDate: 2017-03-23T14:37:17.583Z
Answers.LastEditDate: 2017-03-23T14:37:17.583Z
Answers.UserDisplayName: Andrew West
Answers.Body: <p>This is an oversimplification certainly but with either PostgreSQL or MySQL/MariaDB with their Spatial Extensions installed, you can perform basic <a href="https://dev.mysql.com/doc/refman/5.7/en/spatial-operator-functions.html" rel="nofollow noreferrer">geometry operations</a> 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.</p>
  
  <p>See also <a href="https://stackoverflow.com/questions/8165065/finding-all-areas-that-intersect-with-a-point-and-vice-versa-postgis">this post</a> for more info.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-20T22:55:51.020Z
Answers.LastActivityDate: 2017-03-20T22:55:51.020Z
Answers.LastEditDate: 2017-05-23T12:38:59.763Z
Answers.UserDisplayName: John Twyman
Answers.Body: <p>This isn't possible out of the box but would be an <em>awesome</em> extension.</p>
  
  <p>Assuming google is nicely geocoding your addresses for you then the minimum steps are:</p>
  
  <ol>
  <li>install a database with spatial functions (either postgres+postgis or recent MySql (<a href="https://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions-object-shapes.html#function_st-within" rel="nofollow noreferrer">looks like >= 5.6 ought to do it</a>)</li>
  <li>populate a table with geometry</li>
  <li>create a custom field (per contact probably best, rather than per address)</li>
  <li>write a function that return the desired polygon ID for a point</li>
  <li>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) </li>
  <li>create a smart groups based on your custom field</li>
  <li>write a post update hook that captures the address changes and calls your function to update the custom field</li>
  <li>crack open a cider, put feet up. You deserve it.</li>
  </ol>
  
  <p>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 <code>null</code> for "unknown" and some other value to represent "out of bounds".</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-21T05:18:11.077Z
Answers.LastActivityDate: 2017-03-22T04:06:42.510Z
Answers.LastEditDate: 2017-03-22T04:06:42.510Z
Answers.UserDisplayName: ErichBSchulz
Answers.Body: <p>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 <a href="https://developers.google.com/maps/documentation/javascript/examples/poly-containsLocation" rel="nofollow noreferrer">https://developers.google.com/maps/documentation/javascript/examples/poly-containsLocation</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-21T10:06:12.527Z
Answers.LastActivityDate: 2017-03-21T10:06:12.527Z
Answers.UserDisplayName: Joe Murray
Answers.Body: <p>Via SE Kurund said: </p>
  
  <p><code>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.</code></p>
  
  <p>Am waiting on link to code. will add when it comes on SE</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-21T19:17:36.223Z
Answers.LastActivityDate: 2017-03-21T19:17:36.223Z
Answers.UserDisplayName: petednz - fuzion
Answers.Body: <p>JoeMcl recommended that you take a look at this <a href="https://dc2014.civicrm.org/got-data-now-what-creating-effective-data-strategy-using-civicrm" rel="nofollow noreferrer">https://dc2014.civicrm.org/got-data-now-what-creating-effective-data-strategy-using-civicrm</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-21T19:50:36.923Z
Answers.Text: Thanks Pete, folks should jump into https://chat.civicrm.org/civicrm/channels/town-square to find out more about what Tony Mazzarella presented in 2014 and what @kurund might be able to share about the work that WebAccess did in 2014.
Answers.UserDisplayName: Joe McLaughlin
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-20T19:26:14.520Z
Answers.LastActivityDate: 2017-03-20T19:26:14.520Z
Answers.UserDisplayName: petednz - fuzion
Body: <p>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.</p>
  
  <p>As all contacts in the UK have country 'United Kingdom' this is not easy.</p>
  
  <p>Postcodes in the UK do not correspond to the borders between England/Scotland and England/Wales, and overlap significantly in some areas.</p>
  
  <p>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’).</p>
  
  <p><strong>Would it be possible to geocode the entire areas of England, Scotland, or Wales around known coordinates, and create smart groups based on these?</strong></p>
  
  <p>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. <a href="https://www.google.co.uk/maps/place/Scotland/@56.367676,-4.9607288,8z/data=!4m5!3m4!1s0x4861e2c403f2a19f:0xe7c1fad809c30714!8m2!3d56.4906712!4d-4.2026458" rel="noreferrer">https://www.google.co.uk/maps/place/Scotland/@56.367676,-4.9607288,8z/data=!4m5!3m4!1s0x4861e2c403f2a19f:0xe7c1fad809c30714!8m2!3d56.4906712!4d-4.2026458</a>), that way other regions could also be quickly turned into Smart groups.</p>
  
  <p>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.</p>
  
  <p><a href="https://i.sstatic.net/4wbV6.png" rel="noreferrer"><img src="https://i.sstatic.net/4wbV6.png" alt="enter image description here"></a></p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-03-20T18:29:22.133Z
Comments.Text: I haven't heard of anyone who has done this by adding proper GIS coordinates, but have poked Erich and others on https://chat.civicrm.org/civicrm/pl/aad9pzbe63d33runoyhkcpb8iy
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-03-20T23:11:54.867Z
Comments.Text: and there is another thread about spatial stuff here. https://chat.civicrm.org/civicrm/pl/8aprkamek3r4ucw1bitjjzsc3h
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-03-21T19:07:53.583Z
Comments.Text: wow - this hit a rich vein of information eh.
Comments.UserDisplayName: petednz - fuzion
Tags: <smartgroups><geocoding><postcode>
Title: Search for contacts within Geocoded regions/borders
ThreadText: # Search for contacts within Geocoded regions/borders
  
  (Asked by: username_0 on 2017-03-20)
  
  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.
  
  [![enter image description here](https://i.sstatic.net/4wbV6.png)](https://i.sstatic.net/4wbV6.png)
  
  **Tags:** smartgroups, geocoding, postcode
  
  > **Comments:**
  > **username_1**: I haven't heard of anyone who has done this by adding proper GIS coordinates, but have poked Erich and others on https://chat.civicrm.org/civicrm/pl/aad9pzbe63d33runoyhkcpb8iy  
  > **username_1**: and there is another thread about spatial stuff here. https://chat.civicrm.org/civicrm/pl/8aprkamek3r4ucw1bitjjzsc3h  
  > **username_1**: wow - this hit a rich vein of information eh.  
  
  ---
  
  ## 6 answers
  
  ### Answer by username_2 on 2017-03-21. Score: 4
  
  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 @username_5 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>
  
  > **Comments:**
  > **username_0**: Sounds very promising, thanks for outlining your solution. **Would really appreciate taking a look at your extension**. And will defintely contact Veda to see how far they got with this.  
  > **username_2**: No problem - ping me an email at awest@humanism.org.uk and I'll send it over.  
  > **username_3**: @username_2 that sounds similar to what Jon Goldberg did using the Open States API ( http://docs.openstates.org/en/latest/api/ ) for getting district info US geocoded addresses in Civi, and what username_6 and crew have done using https://represent.opennorth.ca/api/ for Canadian addresses in Civi. Could you possibly share the "hacky" extension in a public repo as well?  
  > **username_3**: See https://github.com/JMAConsulting/biz.jmaconsulting.petitionCanada for the extension repo for Canada and https://hq.palantetech.coop/projects/electoral-api/wiki/User_Documentation for the extension repo for the US.  
  > **username_3**: Have to give credit to Edsel Lopez for his work on the Canada extension, and Joseph Lacey for his work on the US extension, see links above.  
  > **username_2**: Just published our extension at: https://github.com/bhahumanists/bha.politicaldata. This is the first one I've made public, so please do let me know if I've made any mistakes...  
  > **username_3**: @username_2 a very belated thank you for publishing your extension!  
  
  ---
  
  ### Answer by username_4 on 2017-03-20. Score: 3
  
  This is an oversimplification certainly but with either PostgreSQL or MySQL/MariaDB with their Spatial Extensions installed, you can perform basic [geometry operations](https://dev.mysql.com/doc/refman/5.7/en/spatial-operator-functions.html) 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](https://stackoverflow.com/questions/8165065/finding-all-areas-that-intersect-with-a-point-and-vice-versa-postgis) for more info.
  
  
  ---
  
  ### Answer by username_5 on 2017-03-21. Score: 2
  
  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](https://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions-object-shapes.html#function_st-within))
  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 by username_6 on 2017-03-21. Score: 2
  
  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 by username_1 on 2017-03-21. Score: 1
  
  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
  
  
  ---
  
  ### Answer by username_1 on 2017-03-20. Score: 1
  
  JoeMcl recommended that you take a look at this <https://dc2014.civicrm.org/got-data-now-what-creating-effective-data-strategy-using-civicrm>
  
  > **Comments:**
  > **username_3**: Thanks Pete, folks should jump into https://chat.civicrm.org/civicrm/channels/town-square to find out more about what Tony Mazzarella presented in 2014 and what @kurund might be able to share about the work that WebAccess did in 2014.  
  
  ---
  
  