This blog is all about finding, converting, and adding geo data from a commonly found format (shapefiles) to a format that can be used by json engines like Elasticsearch.

Finding Data

Open geo data can be found in a lot of places. Open city data is a great source of geo data in many jurisdictions. Searching for “open data <cityname>” can yield a lot of results. For example, https://datasf.org/opendata/ is San Francisco’s open data portal. Some jurisdictions will have dedicated GIS portals.

You’ll often find geo data in a few formats:

1. A CSV of geo points
2. Shapefiles for geo points
3. Shapefiles for geo shapes
4. WKT (Well-known text)
5. GeoJSON

Elasticsearch natively supports WKT and GeoJSON and I’ll leave the work to import CSVs as an exercise to the reader for now. I’m going to focus this on how to convert/import shapefiles. Sometimes GeoJSON has a full FeatureCollection which is essentially a full layer of features, that, at least for Elasticsearch, needs to be converted to a list of individual Features (points and shapes). I will cover that here in Breaking a GeoJSON FeatureCollection up

In this example, we’ll use the counties in Atlanta, which can be found at http://gisdata.fultoncountyga.gov/datasets/53ca7db14b8f4a9193c1883247886459_67. You can go to Download -> Shapefile to get the shapefile zip file. In this counties example, this looks like this once I’ve unzipped:

$ ls  
Counties_Atlanta_Region.cpg Counties_Atlanta_Region.dbf Counties_Atlanta_Region.prj Counties_Atlanta_Region.shp Counties_Atlanta_Region.shx

The Wikipedia article on shapefiles has a breakdown of what all of these files actually contain, but we only need 1 file for the rest of this exercise: the .shp file, which contains the feature geometries itself.

Converting Shapefiles to GeoJSON

After you have a shapefile, the next step is to get the data into GeoJSON format.

Looking at the Atlanta counties again, the Counties_Atlanta_Region.shp file is the one that’s interesting to us. We’ll use a tool called ogr2ogr to convert .shp files to GeoJSON. ogr2ogr is part of GDAL and can be installed on a Mac if you have homebrew installed by using:

brew install gdal

Alternatively, you can install it manually. ogr2ogr is a wonderful tool to have on your laptop for using/testing geo data. Once you have it, continuing with our example, you should be able to run:

ogr2ogr -f GeoJSON -t_srs crs:84 output_counties.json Counties_Atlanta_Region.shp

This means:

• -f GeoJSON: Output to GeoJSON format
• -t_srs crs:84: There are a lot of coordinate reference systems. If you know you need data in a different coordinate system, you can override this with something else, though that’s going to generally be a highly specialized case. We’re telling ogr2ogr to use WGS84 on the output, which is the same system that GPS uses.
• output_counties.json is the output file
• Counties_Atlanta_Region.shp is the input file.

After you run this, you now have a GeoJSON file! You can inspect this file if you like to see what a GeoJSON file looks like if you’ve never had a look at one.

Breaking a GeoJSON FeatureCollection Up

If we look at the resulting GeoJSON file in the previous step, we see at the top of it:

“type”: “FeatureCollection”, “name”: “Counties_Atlanta_Region”, “crs”: { “type”: “name”, “properties”: { “name”: “urn:ogc:def:crs:OGC:1.3:CRS84” } }, “features”: [ …

Elasticsearch, like several systems that handle geo data, handles most GeoJSON, but FeatureCollections are essentially an array of objects that want to separately index for most purposes. FeatureCollections are sort of like a “bulk” dataset and we need to get individual points/shapes (Features) so that we can search, filter, and use them. In this example, the individual features are individual counties in Atlanta. This is where jq comes in handy.

jq can also be installed via homebrew:

brew install jq

Afterwards, you can do “select the array of features[] from output_counties.json, and output 1 feature per line” by

jq -c ‘.features[]’ output_counties.json

The -c flag means “compact” — it outputs 1 feature per line, which can be useful for what we’re about to do next…

Simultaneously Extracting Features and Converting to Bulk Format

We can do 1 step better than just extracting the features array by simultaneously converting the output to Elasticsearch’s bulk format with sed:

jq -c ‘.features[]’ output_counties.json | sed -e ‘s/^/{ “index” : { “_index” : “geodata”, “_type” : “_doc” } }\

/’ > output_counties_bulk.json && echo “” >> output_counties_bulk.json

The sed bit just adds a bulk header line (and a newline) per record and the echo "" >> output_counties_bulk.json makes sure the file ends in a newline, as this is required by Elasticsearch.

Change geodata to an Elasticsearch index name of your choosing.

Set Up Elasticsearch Mappings

At this point, I’d set up the Elasticsearch mappings for this “geodata” index (or whatever name you want to give it). Metadata related to the shape is often in .properties and geo shape data is often in .geometry. The county data here looks typical:

jq -c ‘.features[].properties’ output_counties.json

Shows us a list of properties like:

{“OBJECTID”:28,”STATEFP10″:”13″,”COUNTYFP10″:”013″,”GEOID10″:”13013″,”NAME10″:”Barrow”,”NAMELSAD10″:”Barrow County”,”totpop10″:69367,”WFD”:”N”,”RDC_AAA”:”N”,”MNGWPD”:”N”,”MPO”:”Partial”,”MSA”:”Y”,”F1HR_NA”:”N”,”F8HR_NA”:”N”,”Reg_Comm”:”Northeast Georgia”,”Acres”:104266,”Sq_Miles”:162.914993,”Label”:”BARROW”,”GlobalID”:”{36E2EA48-1481-44D7-91C9-7C51AC8AB9E9}”,”last_edite”:”2015-10-14T17:19:34.000Z”}

At this point, you can add any mappings around these fields and/or use an ingest node pipeline to manipulate the data prior to indexing. For now, I’m just going to set up the geo_shape field, but you can add extras.

PUT /geodata  
{  
  "settings": {  
    "number_of_shards": 1  
  },  
  "mappings": {  
    "_doc": {  
      "properties": {  
        "geometry": {  
          "type": "geo_shape"  
        }  
      }  
    }  
  }  
}

Bulk Loading Data to Elasticsearch

And at this point, you can bulk-load the data

curl -H “Content-Type: application/x-ndjson” -XPOST localhost:9200/_bulk –data-binary “@output_counties_bulk.json”

And then you can set up or reload Kibana index patterns for your index to make sure it shows up. Make sure to change any time filters to be appropriate with any visualizations you use. I often turn off the “time” field for quick demos as it can often be inconsistent/missing dates (as I found this Atlanta county data to be).

Recap / TL;DR

Get a shapefile

ogr2ogr -f GeoJSON -t_srs crs:84 your_geojson.json your_shapefile.shp

jq -c ‘.features[]’ your_geojson.json | sed -e ‘s/^/{ “index” : { “_index” : “your_index”, “_type” : “_doc” } }\

/’ > your_geojson_bulk.json && echo “” >> your_geojson_bulk.json

Set up your mappings. Often the following works, but you may need to check field names:

PUT /geodata  
{  
  "settings": {  
    "number_of_shards": 1  
  },  
  "mappings": {  
    "_doc": {  
      "properties": {  
        "geometry": {  
          "type": "geo_shape"  
        }  
      }  
    }  
  }  
}

curl -H “Content-Type: application/x-ndjson” -XPOST localhost:9200/_bulk –data-binary “@your_geojson_bulk.json”

Set up (or refresh) Kibana index patterns to include your_index.

Voila!

tl;dr: you can get a new person/car/pet/whatever naming app now at https://github.com/eskibars/lentil-namer

As a first-time dad-to-be, one of the things I’ve noticed is that the first thing anyone asks you about your baby is if you know the gender. The second thing they ask you is if you have names picked out. The first one is kind of easy: you’ve either decided to learn about the baby via e.g. an ultrasound or you’ve chosen to not — but ultimately it’s not really up to you. The second one is harder: you’re given a choice to name someone that’s going to be something they probably will live with for the rest of their life.

One of the things I’ve learned in my time as a product manager is that naming things is the hardest thing in virtually any profession. Naming a human is doubly so. Agreeing on a name for a human between 2 separate humans… that’s the hardest: you have no chance for quorum without unanimity; the number of reasonable options for you to both agree on is virtually limitless; and it’s difficult to feel you actually each had an “equal” part in the name given that one of you is likely to propose some name first. This is where new baby naming apps like this have come up: swipe left/right to suggest you like a name and your partner will be able to swipe as well. If it’s a match, ta-da! You’ve found one you both like. No bias, no judgement on proposing “silly” names, and no feeling bad about shooting down your partners’ name idea that you think is silly.

However, I got frustrated with the options that were out there for this problem. They all require some application installation with sign-ups and unknown data sharing practices for what I felt like was a pretty simple problem. They also often didn’t work well either with iOS or Android or didn’t allow you to participate across the two. And finally, many didn’t give you a lot of options like choosing the origins of the name or showing you the meaning. Perhaps most importantly, I was interested in trying my hand at writing a small application that took advantage of the block-max weak-and (WAND) that we released as part of Elasticsearch 7. It’s quite nifty in that it lets you bias certain results and return them with speeds that can be orders of magnitude faster than 6.x or earlier as long as you don’t need a total count of hits at result time. (In fact, there is no bound as to how much faster this algorithm can be.)

So with a baby on the way I set about to try my hand at creating something useful with block-max WAND: a way for my wife and I to name our future child without bias between ourselves!

The software

I wrote a bit of software which attempts to overcome some of the limitations I’ve seen in these apps. It:

1. Runs as a web application which means you can load it in any browser (including mobile) and swipe left/right. No app install / you can control your own data
2. There’s no sign-up required. You start it up, start a project, and you’re good to go. No data-sharing with unknown 3rd party companies. You can host it yourself.
3. Lets you bias the origin and the gender of the baby
4. Lets you review and edit any of your past answers

The flow looks something like this:

Once you swipe left/right, you’ll find the names in the the answer review / mutual matches and can switch your answers if you so please. If you find them in the mutual matches, that may be a good one to discuss with your partner!

The primary things that makes this stand out to previous applications I’ve written using Elasticsearch is that it

1. Uses rank_feature fields, which are new to Elasticsearch 7 and really make the performance scream
2. Is built on flask, which I have no experience with until now. I also have virtually no Python experience, so that was maybe even a more extreme difference
3. I’ve licensed it as AGPL and released it on GitHub

What’s in a name?

The name for this comes from yet another thing we found: apps that tell you the size of your baby as it progresses. For example, “this week, your baby is the size of a grape” or “this week, your baby’s foot is the size of a raspberry.” I came up with the idea for this right around the time we found out that our baby was the size of a lentil. So it’s a lentil namer, and hence the GitHub project name 🙂

What’s next?

We’ve used this (and some old fashioned suggestions) to decide on a number of names that we both like and now we’re down to picking our name. We don’t plan to reveal until after it’s born. Sorry friends & family 🙂

Occasionally, I’ve had the need to populate sample users into an Active Directory for projects.  There are, no doubt, other ways to do this that are simpler/more direct.  For example, it’s likely incredibly easy using PowerShell (but I have never been a PowerShell person) and even in the Perl world, there are Perl modules to interact with LDAP.  So using OLE may not seem like the ideal way, and that’s probably right, but OLE comes with virtually every Windows Perl distribution I’ve seen (as opposed to LDAP) and it isn’t terribly difficult.  In general, I haven’t seen too many examples of using OLE to access this type of data on Windows systems, so I wanted to write a brief post about how it can be used for those that stumble upon it.  It’s also nice that, by virtue of running through OLE, you don’t need anything special for authentication here, as that will be automatically inherited by your active running user.

First things first

In this example, I’ll assume you’ve at least got an Active Directory set up and that it’s generally fairly vanilla.  We need to import the Win32::OLE bits into our script, which is relatively straightforward

use Win32::OLE 'in';

Next, we need to know a bit about the Active Directory that’s been set up.  If you don’t know the structure of it, you can use a variety of tools including the open source LDAP Explorer or Microsoft’s Sysinternals Active Directory Explorer.  You’ll need:

• The domain to put things in, in LDAP format
• The base path to put the new users/groups in
• To be a user that has access to create users (and groups, if you’re looking to do so) at the given base path
• To create or use some format of e-mail addresses for the new users
• An LDAP URL to access the system

So in this example, we’ll assume I’ll be putting users into customdomain.sfdemo.mysite.com (LDAP format of “DC=customsubdomain,DC=sfdemo,DC=mysite,DC=com”) and giving them an e-mail address of “firstname.lastname@mysite.com”.  The base path will be “OU=UserGroup,OU=DemoUsers,DC=customsubdomain,DC=sfdemo,DC=mysite,DC=com”

So let’s go define these in Perl

my $domain = 'DC=customsubdomain,DC=sfdemo,DC=mysite,DC=com';
my $newpassword = 'pa55w0rd!';
my $emaildomain = 'mysite.com';
my $basepath = 'OU=UserGroup,OU=DemoUsers,' . $domain;
my $ldaphost = '127.0.0.1';
my $ADsPath = 'LDAP://' . $ldaphost . '/' . $basepath;

Once we have this all defined, we can go grab the OU object and use it later

my $ou = Win32::OLE->GetObject($ADsPath) or die "Unable to get $ADsPath\n";

So to start out with, we’ll do something pretty easy: adding a security group.  Let’s say we want to call this group “LegalUsers”.  At its simplest, adding the group is just

1. Creating the group
2. Assigning an the “sAMAccountName” property to it for Active Directory
3. Updating (saving) the object:

my $securitygroup = 'LegalUsers';
my $newgroup;
$newgroup = $ou->Create('group','CN=' . $securitygroup);
$newgroup->{sAMAccountName} = $securitygroup; #add the sAMAccountName property
$newgroup->SetInfo( ); #save the object
$newgroup = Win32::OLE->GetObject('LDAP://127.0.0.1/cn=' . $securitygroup . ',' . $basepath) or die "Unable to find OU\n"; #retrieve the updated object for later reference

More complex: Adding a user

The only thing that makes adding users more complex is that there are more fields to add.  We need to add the name and set the path of the user in AD and add an e-mail, but may also want to give them a default password, change their account expiration properties, assign them to groups, etc.  Let’s walk through an example of this…

#For this example, we'll show 1 particular username.  The bulk import later will make more sense with this
#Although the bulk import will just take a flat 1-line-per-username format, a CSV could be devised and used instead
my $username = 'shane.connelly';
my $email = $username . "\@$emaildomain"; #our new e-mail, as in shane.connelly@mysite.com
my @namearray = split(/\./, $username);

my $realname = $username;
my $fullname = join(' ', map (ucfirst, @namearray)); #name will now be upper-cased first letters with a space as in "Shane Connelly"
my $initials = uc(join('', map (substr($_,0,1), @namearray))); #initials will now be the first letters of each word, as in "SC"
$initials = substr($initials,1,length($initials) - 2);
my $lastname = ucfirst($namearray[$#namearray]);

print "Adding $fullname\n";

#As with the groups, simply creating a user is fairly easy.
#The only thing that really makes this different than any other LDAP import is that most AD systems have some specific schema as to the field names you use
#The below works with that schema
my $u = $ou->Create("user", 'cn=' . $fullname) or die "Unable to create user\n";
$u->{sAMAccountName} = $username; #as with the groups, we need a sAMAccountName and this needs to be unique
$u->{company} = 'XYZ Corporation';
$u->{initials} = $initials if ($initials);
$u->{displayName} = $fullname;
$u->{name} = $fullname;
$u->{givenName} = ucfirst($namearray[0]);
$u->{sn} = $lastname; #sn = surname
$u->{mail} = $email;
$u->{mobile} = ('(' .(100 + int(rand(800))) . ') ' . (100 + int(rand(800))) . '-' . (1000 + int(rand(8000)))); #create some random phone number for demonstration purposes
$u->{co} = 'United States'; #co = country
$u->{c} = 'US'; #c = country code
$u->SetInfo(  );

#We'll give the user a default password.  For this example, every user will have the same password, which is obviously quite insecure
$u = Win32::OLE->GetObject('LDAP://127.0.0.1/cn=' . $fullname . ',' . $basepath) or die "Unable to find last added user\n";
$u->ChangePassword("",$newpassword);
$u->SetInfo(  );

#Set some flags for the user
#For this example, we'll say "Password doesn't expire" (0x10000 or 65536 in decimal) and make it a "Normal account" (0x0200 or 512 in decimal)
#Just sum up the flags (0x10000 + 0x0200 = 0x10200 or 65536+512 = 66048 in decimal)
#There are a number of flags you can add to a user account, available at http://support.microsoft.com/kb/305144
$u->{userAccountControl} = '66048';
$u->SetInfo(  );

#perhaps we want to also add the user to the "Remote Desktop Users" group
my $g = Win32::OLE->GetObject('LDAP://127.0.0.1/cn=Remote Desktop Users,CN=Builtin,' . $domain) or die "Unable to find last remote desktop group\n";
$g->Add('LDAP://cn=' . $fullname . ',' . $basepath);
$newgroup->Add('LDAP://cn=' . $fullname . ',' . $basepath) if ($newgroup);

Tying it all together

For creating demo users, I’d often simply create a flat file with usernames in it and read through them.  You can also add managers, cities, and addresses for a more “real-looking” scenario.

use strict;
use Win32::OLE 'in';

##### CONFIG #####

my $domain = 'DC=customsubdomain,DC=sfdemo,DC=mysite,DC=com';
my $newpassword = 'pa55w0rd!';
my $emaildomain = 'mysite.com';
my $basepath = 'OU=UserGroup,OU=DemoUsers,' . $domain;
my $securitygroup = 'LegalUsers';
my @cities = ('New York:New York','Los Angeles:California','Houston:Texas','Philadelphia:Pennsylvania','Phoenix:Arizona','San Antonio:Texas',
			  'San Diego:California','Dallas:Texas','San Jose:California','Jacksonville:Florida','Indianapolis:Indiana','San Francisco:California',
			  'Austin:Texas','Columbus:Ohio','Fort Worth:Texas','Charlotte:North Carolina','Detroit:Michigan');
my @streettypes = ('Avenue','Street','Circle');
my @streetnames = ('Second','Third','First','Fourth','Park','Fifth','Main','Sixth','Oak','Pine','Maple','View','Washington','Lake','Hill','Cedar','Stuart','Smith');

my @managers = ('CN=Ally Mcbeal,OU=UserGroup,OU=DemoUsers,DC=customsubdomain,DC=SFDemo,DC=MySite,DC=com',
				'CN=Shane Connelly,OU=UserGroup,OU=DemoUsers,DC=customsubdomain,DC=SFDemo,DC=MySite,DC=com');
##### END CONFIG #####

my $ADsPath = 'LDAP://127.0.0.1/' . $basepath;
my $ou = Win32::OLE->GetObject($ADsPath) or die "Unable to get $ADsPath\n";

my $newgroup;
if ($securitygroup)
{
  $newgroup = $ou->Create('group','CN=' . $securitygroup);
  $newgroup->{sAMAccountName} = $securitygroup;
  $newgroup->SetInfo( );
  $newgroup = Win32::OLE->GetObject('LDAP://127.0.0.1/cn=' . $securitygroup . ',' . $basepath) or die "Unable to find OU\n";
}

open(USRS,"users.txt");
while (<USRS>)
{
	chomp;
	my $username = $_;
	$username =~ s/\_/\./g;
	my $email = $username . "\@$emaildomain";
	my %altemails = ();
	my @namearray = split(/\./, $username);
	
	my $realname = $username;
	my $fullname = join(' ', map (ucfirst, @namearray));
	my $initials = uc(join('', map (substr($_,0,1), @namearray)));
	$initials = substr($initials,1,length($initials) - 2);
	my $lastname = ucfirst($namearray[$#namearray]);
	
	print "Adding $fullname\n";
	
	my $u = $ou->Create("user", 'cn=' . $fullname) or die "Unable to create user\n";
	$u->{sAMAccountName} = $username;
	$u->{company} = 'XYZ Corporation';
	$u->{initials} = $initials if ($initials);
	$u->{displayName} = $fullname;
	$u->{name} = $fullname;
	$u->{givenName} = ucfirst($namearray[0]);
	$u->{sn} = $lastname;
	$u->{mail} = $email;
	$u->{mobile} = ('(' .(100 + int(rand(800))) . ') ' . (100 + int(rand(800))) . '-' . (1000 + int(rand(8000))));
	$u->{co} = 'United States';
	$u->{c} = 'US';
	
	my $userlocation = $cities[rand @cities];
	my ($city, $state) = split(":", $userlocation);
	$u->{st} = $state;
	$u->{l} = $city;
	$u->{streetAddress} = int(rand(9999)) . ' ' . $streetnames[rand @streetnames] . ' ' . $streettypes[rand @streettypes];
	$u->{postalCode} = int(rand(99999));
	$u->{userPrincipalName} = $email;
	$u->{manager} = $managers[rand @managers];
	$u->SetInfo(  );
	
	$u = Win32::OLE->GetObject('LDAP://127.0.0.1/cn=' . $fullname . ',' . $basepath) or die "Unable to find last added user\n";
	$u->ChangePassword("",$newpassword);
	$u->SetInfo(  );
	
	$u->{userAccountControl} = '66048';
	$u->SetInfo(  );
	
	my $g = Win32::OLE->GetObject('LDAP://127.0.0.1/cn=Remote Desktop Users,CN=Builtin,' . $domain) or die "Unable to find last remote desktop group\n";
	$g->Add('LDAP://cn=' . $fullname . ',' . $basepath);
	$newgroup->Add('LDAP://cn=' . $fullname . ',' . $basepath) if ($newgroup);
}
close(USRS);