<commit_before>#OpenStreetMap
##Importing OSM Data
The import process is documented numerously on the internet, a good one for Ubuntu is
https://switch2osm.org/serving-tiles/manually-building-a-tile-server-14-04/ - once set up an import
of the OSM data for Thailand took around 20 minutes.

Snapshots of OSM data are available from http://download.geofabrik.de/

##Extracting Points of Interest
The process of extracting points of interest is as follows:
* Select the points you are interested in from the PostGIS database into which the OSM data was
imported.  Use SQL and output to a file.
* Parse the result output using a language of your choice to create SQL that can be imported into
SilverStripe as points of interest.
* Import the resultant SQL into your SilverStripe database.

## Worked Example - Thai Railway Stations
###Extract Data from PostGIS OSM Database
Create a file of arbitrary name, e.g. osm.sql, with the following query, which extracts OSM id,
name, latitude, longitude and tags for nodes:
```sql
select osm_id,name, amenity,
ST_Y((ST_Transform (way, 4326))) as Latitude,
ST_X((ST_Transform (way, 4326))) as Longitude,
tags
from planet_osm_nodes N
inner join planet_osm_point P
on N.id = P.osm_id
;
```

At the command line execute this query and grep for railway stations:
```bash
psql gis < osm.sql | grep -i railway > trains.osm
```
Note that authentication to the Postgres database may differ.

Analysis of the output shows that further grepping is require in order to extract just stations.
```
.77085717602143 | 100.437159389841 | {railway,level_crossing}
 3286015332 | Bang Klam                                                                                               |                            | 7.08769743171173 | 100.415422944743 | {railway,station,name,"Bang Klam"}
 3287950085 | Death Railway viaduct                                                                                   |                            | 14.1534710197064 | 99.1098331602021 | {tourism,attraction,name,"Death Railway viaduct"}
 3288538284 |                                                                                                         |                            | 9.14452066569457 | 99.1675465934402 | {railway,level_crossing}
 3288549665 |                                                                                                         
```
Execute the following to get just the stations:<commit_msg>ENHANCEMENT: Addition of note to add the OSMPointOfInterestExtension<commit_after>#OpenStreetMap
##Importing OSM Data
The import process is documented numerously on the internet, a good one for Ubuntu is
https://switch2osm.org/serving-tiles/manually-building-a-tile-server-14-04/ - once set up an import
of the OSM data for Thailand took around 20 minutes.

Snapshots of OSM data are available from http://download.geofabrik.de/

##Extracting Points of Interest
The process of extracting points of interest is as follows:
* Select the points you are interested in from the PostGIS database into which the OSM data was
imported.  Use SQL and output to a file.
* Parse the result output using a language of your choice to create SQL that can be imported into
SilverStripe as points of interest.
* Import the resultant SQL into your SilverStripe database.

Note that PointOfInterest requires the extension OSMPointOfInterestExtension which can be added as
follows using the standard configuration method in SilverStripe.  It adds a field for the 
OpenStreetMap ID, so that PointOfInterest can be updated appropriately if re-importing with a 
newer version of OpenStreetMap data.

```yml
PointOfInterest:
  extensions:
    ['OSMPointOfInterestExtension']
```

## Worked Example - Thai Railway Stations
###Extract Data from PostGIS OSM Database
Create a file of arbitrary name, e.g. osm.sql, with the following query, which extracts OSM id,
name, latitude, longitude and tags for nodes:
```sql
select osm_id,name, amenity,
ST_Y((ST_Transform (way, 4326))) as Latitude,
ST_X((ST_Transform (way, 4326))) as Longitude,
tags
from planet_osm_nodes N
inner join planet_osm_point P
on N.id = P.osm_id
;
```

At the command line execute this query and grep for railway stations:
```bash
psql gis < osm.sql | grep -i railway > trains.osm
```
Note that authentication to the Postgres database may differ.

Analysis of the output shows that further grepping is require in order to extract just stations.
```
.77085717602143 | 100.437159389841 | {railway,level_crossing}
 3286015332 | Bang Klam                                                                                               |                            | 7.08769743171173 | 100.415422944743 | {railway,station,name,"Bang Klam"}
 3287950085 | Death Railway viaduct                                                                                   |                            | 14.1534710197064 | 99.1098331602021 | {tourism,attraction,name,"Death Railway viaduct"}
 3288538284 |                                                                                                         |                            | 9.14452066569457 | 99.1675465934402 | {railway,level_crossing}
 3288549665 |                                                                                                         
```
Execute the following to get just the stations:
{'fraction_non_alphanumeric': 0.060191261953872116, 'fraction_numerical': 0.06525407837989874, 'mean_word_length': 2.192100538599641, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 44, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6578381', 'n_tokens_mistral': 1617, 'n_tokens_neox': 1311, 'n_words': 552}