# Bivariate Choropleth Maps with Arcpy

In my previous post, I showed how to prepare the data for a bivariate choropleth map using PostGIS and QGIS. I also indicated that there is a website that shows an ArcGIS tool to do it. But, this actually turns into a good opportunity to illustrate some Python, and how to create the bivariate data using Arcpy.

Arcpy is certainly not as terse as SQL, but it does get the job done, and rather easily. We just have to think about the project a little differently. The code below is a Script tool that I created.

```import arcpy, math, numpy
fc = arcpy.GetParameterAsText(0)

numrecs = int(arcpy.GetCount_management(fc).getOutput(0))

fields = arcpy.ListFields(fc, "bimode")
if len(fields) != 1:

f1 = arcpy.GetParameterAsText(1)
f2 = arcpy.GetParameterAsText(2)
fields = ['bimode',f1,f2]

var1 = arcpy.UpdateCursor(fc, sort_fields=f1)

i=1
for row in var1:
row.setValue("bimode",str(int(math.ceil((float(i) / float(numrecs)) * 3.0))))
var1.updateRow(row)
i=i+1

var2 = arcpy.UpdateCursor(fc, sort_fields=f2)

i=1
for row in var2:
row.setValue("bimode",row.getValue("bimode") + "." + str(int(math.ceil((float(i) / float(numrecs)) * 3.0))))
var2.updateRow(row)
i=i+1
```

# Great work by my undergraduates, Again!

You’ve heard about how good my undergraduate GIS students are here, here, and here.  Oh yeah, and here and here.    Well, over the last few years my undergraduate students have been working with the campus Department of Horticulture by surveying the Salisbury University campus under the direction of Dr. Dan Harris.  Did you know our beautiful campus is a registered arboretum?  Starting with my student Waverly Thompson two years ago, they surveyed all the trees, sidewalks, sprinkler heads, light poles, pretty much anything you can think of with survey grade instruments.

This past summer Zack Radziewicz, Josh Young, and Lindsey Pinder turned that survey work into a beautiful cartographic product, and yesterday put the map online here.  Zach has an art background (he’s also an awesome GIS student) so that really helped, and Josh has been helping me lead professional workshops in Postgres and Python.  Lindsey is a rising GIS star in our Department, so keep an eye out for more posts about her.

It never ceases to amaze me how good our undergraduate students are here at SU.  I’m proud of the work they do, and so thankful to get to work with them each day.

Great job Waverly, Thompson Zack, Josh, and Lindsey.

P.S. While I was in Korea and Dr. Harris was in Brazil, on their own, these students turned the entire map into a 3D visualization – I’ll post that soon.

# Cartography in ArcGIS and QGIS

Today I want to introduce you to another one of my students, Meghan Murphy.  Meghan is an outstanding student, and one of the top undergraduates I have ever worked with (I know, I say that a lot, but they just keep getting better and better).  Even as a Sophomore, Meghan was always helping other students out, even the Seniors – students would seem to wait for Meghan to organize everyone together to study for upcoming exams.

She also has an innate ability to work with GIS, and pick up new things: one day she has never programmed in Python, and the next day, she has a couple of hundred line Python script created and running in ArcGIS!  So, I was so happy when Meghan said she wanted to take a special course in Open Source GIS that I was offering this semester.  We covered QGIS, Postgres/PostGIS, GDAL, and Geoserver.  For her final project, Meghan decided she wanted to compare the cartography capabilities of ArcGIS and QGIS, and make a video about it (maybe she was inspired by my videos, or maybe she just figured after watching Lembo’s videos, how could I do worse!).

Whatever her reason, like everything else she does, this turned out great, especially since she had never done a live tutorial like this.  So, I encourage you to watch the side-by-side comparisons for creating a basic cartographic product in both ArcGIS and QGIS.  It’s about 40 minutes long, but worth every minute: I found that I learned some things I hadn’t known regarding some cartographic tools.  And, on that note, I’ll have more videos from my undergraduates shortly (some built web maps, others built an enterprise GIS with Postgres.

If you want to learn more about open source GIS, Python programming, Spatial SQL, or Spatial Statistics, check out my online courses at www.gisadvisor.com.

# The Web Duel – Last Thoughts

The Web GIS Duel: Final Thoughts

This is a continuation of Mark Balwanz’s blog posts on his creation of web mapping sites using both ESRI and Geoserver.  Today he will talk about his experience creating the site using open source technology.

Over the first three parts of this blog series (here, here, and here), I have laid out my project plan, walked you through my ESRI implementation, and my Open Source implementation. During this fourth and final blog I plan on sharing with you my overall thoughts on both implementations in regards to what I liked and disliked. As I mentioned in Part 1 of this series, everything I share here is just my opinion and is based on this one project. I also want to point out that most of my previous experience, both academic and professional, is based in ESRI and is probably shaping some of my opinions.

I will start by sharing my opinions about my experience building the ESRI version. I found the ESRI build to be quite simple thanks to the enormous amount of information that can be found online. Since the entire stack is from the same company the integration between ArcGIS Desktop, ArcGIS Server, and the API was seamless and very easy to navigate. Publishing data to ArcGIS Server was a breeze thanks to the publishing tools that they have included in the desktop software. I also found it extremely easy to pull ArcGIS Server map services into the application that was built with the API. None of this should come as a surprise since these things were built by the same company and are meant to work together. Another positive I found with ESRI is their documentation. The ArcGIS for Developers website and specifically the JavaScript samples and API reference page contained everything I needed to build my application. The online community of users is quite large as well which provides a tremendous wealth of information within forum posts. I think one of the huge benefits that the ESRI implementation provided was the ArcGIS Server capabilities; the identify task, geometry service (although I did not use it), and many others. I found using ArcGIS for Server to be much easier than GeoServer when it came to writing JavaScript code to query the services. I really did not run into anything with the ESRI implementation that made frustrated me. Of course, if I was building something like this for a company I would have to factor in the cost of the systems as well. The ESRI stack has the advantage of being built by a single company with a single vision of how everything should fit together, and that is something that by its nature the Open Source stack will never have.

So as you can tell I enjoyed my time building the ESRI version, but was really excited to see how the Open Source one would compare. I think I mentioned this in Part 3 of this series, but I was once again very impressed with QGIS. I found it very intuitive and the amount of plugins that you can add is very impressive. The GeoServer Manager plugin made the publishing of WMS to GeoServer a trivial task. As mentioned above, I do prefer ArcGIS Server over GeoServer, but that being said the GeoServer manager page is very easy to use and seems to be well thought out. The open source JavaScript libraries were also quite impressive. Although I ended up switching from Leaflet to OpenLayers 3, I still came away impressed by how easy it was to use Leaflet was and will be looking to use it in the future. The one problem I had with OpenLayers was how hard it was to find accurate information about OpenLayers 3, online. Most everything I found was for OpenLayers 2 and that does not migrate very well to OpenLayers 3. Even the book I bought for OpenLayers 3 had some code that is not included in the library anymore and therefore did not work. The samples page on the OpenLayers 3 website was helpful, but overall I was not impressed by their documentation. Another area where I felt was lacking was the integration of GeoServer and OpenLayers. I found it complicated to perform relatively simple spatial queries against a GeoServer WMS from within OpenLayers. What made this more difficult is that I had to search through two sets of documentation to solve my problem (GeoServer and OpenLayers 3) rather than just one. I do think that some of these problems would probably decrease the more I used GeoServer and OpenLayers, but better documentation would make it easier for people to jump into the Open Source world. After completing this project though, I am pretty confident that the Open Source world has the capabilities to match ESRI and the fact that the software is free is a huge benefit. Additional work to more seamlessly integrate these Open Source projects would go a long in making them more user friendly.

As someone who has spent most of his GIS life working with ESRI, I have to admit it was a little uncomfortable to move into the Open Source world. However, being uncomfortable was a good thing as it pushed me to learn a lot of new technologies. I think Open Source can be a great way for organizations to start using GIS as there is a lot less upfront cost and with a little research you can find the Open Source project that best fits your need.

I hope you have enjoyed this blog series and have maybe learned something that you can use. Please feel free to leave comments if you have any questions and thanks for reading!

I just got the new build of Radian Studio, and now that it can directly read geodatabases, you can link directly to the geodatabase from inside of Radian and perform Radian spatial queries on it.  In this video, I’m linking directly to an ESRI geodatabase, creating a small map display of the data, performing a spatial clip of two vector layers, and returning the results.   In my previous video, I showed how Radian studio can read data directly from PostgreSQL, SQLite, and also easily exchange data between them.   This is just another example of how Radian can manage disparate GIS databases.

l hope you like the video, if so, consider learning more about Radian Studio in my online course here.

# How do I do that in Arcpy

In 2004, I created a little document with my students titled How do I do that in ArcGIS/Manifold.  To our surprise, the document really took off, and had tens of thousands of downloads from all over the world.  It was that document, and the response, that got me to realize how as a Professor, I could have a far reaching impact on people learning GIS.

As the years passed, I came out with a number of other documents in the series: How do I do that in Manifold SQL, How do I do that in QGIS, and How do I do that in PostGIS.  These documents have also been used by thousands of people (although, nowhere near the reach of the original document).

So  today I am posting my latest book in the series: How do I do that in Arcpy: Illustrating Classic GIS Tasks.  I love this book: it is short and to the point, and actually provides users with code to illustrate all the commands that the USGS’s 1988 document A Process for Evaluating Geographic Information Systems said should be in any GIS.  Between Manifold, PostGIS, QGIS, and Arcpy, I can’t keep all these languages straight (the effects of aging, I suppose).  So, I keep each of these books right next to my computer for a quick reference on how to do virtually any GIS task.

I welcome you to download the .pdf and make use of it yourself with the accompanying geodatabase..  You are free of course to send it to whomever you like, but I would appreciate it if you simply provided people with a link to my site so they can see more of what I’m doing here.

If you want to learn more about how to program geospatial tasks with Python, or how to use Free and Open Source GIS like QGIS and PostGIS, check out all my courses here.  And, in celebration of the book, you can get my course Python for Geospatial for \$20 here.

This week my GIS Programming students presented their programming projects to ESRI. First, I cannot say enough to thank ESRI for taking time out of their schedule to meet with our students – the staff was helpful, encouraging, and provided great feedback to the students – what an honor it was to get their feedback.  I am so thankful to be a part of a GIS community that is so supportive of one another.

Now, this was a really special class of undergraduates – and some of them were part of that special group of students that presented their research at an undergraduate conference.  It was small, so we could do some really cool things.  In fact, in the middle of the semester, the students wrote a paper comparing the geocoding accuracies of Google Maps and the United States Census Bureau.

Things were going so well that I decided in lieu of a final exam, we expanded their final projects a little more, and arranged for the staff at ESRI Charlotte and ESRI Redlands to join us on a WebEx that included demonstrations and a code walk-through.  Below are each students’ presentation, and some of the Q&A from ESRI:

Noah Krach.  Noah is an amazing undergraduate.  Recently, we lost one of our graduate research associates, and Noah stepped in to provide technical support on a National Science Foundation project in Lake Victoria.  Without missing a beat, Noah was all over the project, and he used his time in my class to create an Arcpy tool to extract, translate, and load (ETL) gigabytes of Landsat imagery.  This tool does a lot, and I can’t even begin to describe all he did, you’ll simply have to watch and learn.

Check out his video, and you’ll see why we are so excited that Noah will be around for another semester.

Caitlin Curry.  If you follow my blog, you’ve already met Caitlin.  She finished her summer internship I told you about, and during the middle of it, her boss wrote us to say what an excellent worker she was (he prefaced his email by saying he never does that, but was so impressed with Caitlin, he had to let us know).  We are impressed with Caitlin, too.  And, as I have now grown to expect, Caitlin did an amazing job with another ETL type tool using Arcpy, where she downloaded, unzipped, and processed earthquake data and critical infrastructure.

I did a lot of emergency response work with earthquakes in a previous life, and what Caitlin did here would have been so useful.  I think you will enjoy seeing how she integrated many different Python packages with Arcpy to provide an early warning application for emergency responders.  And just as a heads-up, Caitlin uses Python to download everything while the script is running – so you just give the script to a user and it works without any operator knowledge of the underlying data = really cool, and efficient.

Matthew Bucklew.  After my first lecture this semester, Matt told me he built his own computer this summer – just for fun.  So, I knew he wasn’t your ordinary  geographer – he likes to try new things, and if something is done in a conventional way, Matt is going to try and be more innovative.  Matt created a great Arcpy application to locate renewable engery stations needed by automobiles.  His Python scripts use ArcGIS for analysis, but at the same time, seamlessly brings in the Google APIs to provide directions to the nearest locations.  For good measure, he also brings in other packages like heapq.

At the moment, Matt’s program works on a desktop, but his hope it to turn this application into a cloud based solution for use with mobile phones.  Keep an eye out for what Matt comes up with, and if you watch this, you’ll see it is an excellent tutorial on how to mash up bunches of Python packages with Arcpy.

Jessica Molnar.  Like Caitlin, Jessica is another student you’ve seen before.  She’s got such a big heart, and is always looking for ways to apply GIS to humanitarian and ecological solutions.  In this project, Jessica created an Arcpy application to identify locations for community gardens in Baltimore City with special consideration for locations within food deserts, near churches and schools, and on suitable soils for growing food.  Jessica’s program also found those locations that were already owned by the City, but were vacant.  Let’s hope the City makes use of this to build a more beautiful Baltimore (BTW, Jessica wrote her program to work in any location in the State of Maryland, so any community can use this tool!).  I think Jessica may eventually roll this into a cloud based solution – hey Jessica, I think we found a project for graduate school!

John Tilghman.  John’s family owns an orthodontist practice, and John decided to use PostGRES/PostGIS along with a number of other different Python packages to perform market area analysis.  John integrated PostGRES, Google, and the Pygal libraries to create the first stages of a geodashboard to assess the effectiveness of marketing strategies, and other metrics.  In the video, you’ll also see how he created a distance decay algorithm in SQL to determine at what point customers drop off from visiting the practice.  With just a little bit of information (addresses and marketing strategies), John was able to extract a ton of business information – in fact, our guests from ESRI were surprised the John wasn’t already a business major!

This is an excellent presentation to watch for those of you who are interested in using Python with Open Source GIS – you’ll learn how to integrate FOSS4g and Python for a business analytics tool.

Josh Young.  Josh created an Arcpy script to assemble tons of location based data that might be useful for someone thinking about moving to a particular location.  Now, in Josh’s case, he chose location based data he deemed important for the neighborhood (download speeds, elementary school, crime statistics, distance to the downtown, etc.).  But ultimately, what Josh has shown us is how to create a template that integrates multiple Python packages and online data to provide very useful information.

It would be so easy to take Josh’s work and roll it into a site specific location-based analysis engine.  In fact, one of the people watching Josh’s presentation mentioned that he was moving, and saw how useful this could be for a community.  The best part of it is that Josh did it with all freely available online data for the State of Maryland, so any community can spin this up into a cloud-based solution.

Robbie Stancil.  Robbie is our only non-geography major.  You’ve met him before when he worked with me on a National Science Foundation project to use Spatial Hadoop.  Like John, Robbie’s project used Postgres/PostGIS and the Google API to do something quite interesting: he created a mesh of points over community to determine how far the Google API will search in order to find a property address, and compared the concave hull of each series of points for an address to the actual property parcel.  This project got us thinking about some very creative uses – you’ll have to watch it until the end to see the interesting things we came up with.

Again, I have to give a huge shout out to the ESRI staff – they were wonderful guests, and really excellent mentors during the Q&A. As these students get ready to graduate in May, I know they will make excellent employees or graduate students – the future is really bright for them. If you are in academia, I hope that you are inspired to expect the very best of your students as I do, and you’ll be so pleased to see what they are capable of doing.

want to learn how to program geospatial solutions like these students? Check out the geospatial courses at gisadvisor.com.

our merry band of 12 undergraduate GIS students presenting their research posters at SUSRC.

Today was the Salisbury University Student Research Conference (SUSRC).  I love the SUSRC because it represents what is so great about Salisbury University: the undergraduate experience.  Our university prides itself in giving undergraduates a graduate school experience.  As a friend once told me, when you come to Salisbury University, you’ll find yourself in the lab, with a Professor, with a PhD.

Salisbury University has really shown me the joy of working with undergraduates.  I get to watch them enter as immature 18 year old freshmen, get excited about quantitative geography when they take Statistical Problem Solving in Geography their sophomore year, and continue to mature as they take GIS Programming and Advanced GIS, along with so many of the other great course offerings in Geography and Geosciences. Continue reading

# Trade Area Analysis in Postgres / PostGIS

How I used SQL to generate an accumulated sum of spatial data

My friend Tomas does work with business analytics, and wanted to find a way to perform trade area analysis.  He had a bunch of stores, and a map of census areas with populations.  What he wanted to figure out was:

how far do I have to radiate out from each store before I hit 5,000 customers

So for each store, he wanted to basically generate concentric buffers and sum up the population of the census areas before he hit 5,000.  Once he found the census areas, he also wanted to generate a convex hull to show the area.  ESRI has a really nice tool that performs this as part of their business analyst package, called threshold trade areas.

Check it out, it seems pretty cool.

Well, to help my friend I was thinking that I would determine the distance from every store to every census area, and then write a script with a giant for loop to iterate through each record, accumulating the results (of course, I would have to integrate some kind of do..while loop and an if…then to make sure I accumulated different counts for each store until I hit the threshold I wanted.  At that point I began asking myself so, how good of friend is Tomas?

What I did instead was write an SQL script to do it. I’ve color coded it below to explain what I was doing….

```SELECT ST_ConvexHull(ST_Collect(g)) as geometry, max(sumpop) as sumpop, name
FROM
(
SELECT a.name, SUM(a.totpop) AS sumpop, ST_Collect(a.geometry) as g
FROM
(SELECT stores.name, censusunit.totpop, censusunit.geometry,
ST_Distance(censusunit.geometry,stores.geometry) as dist
FROM stores, censusunit
) AS a,
(SELECT stores.name, censusunit.totpop, censusunit.geometry,
ST_Distance(censusunit.geometry,stores.geometry) as dist
FROM stores, censusunit
) AS b
WHERE a.name = b.name
AND a.dist <= b.dist
GROUP BY a.name, b.dist
) AS T1
WHERE sumpop < 5000
GROUP BY name```

The middle portion in orange collects the names of the stores, the population of the census areas, the distance between each store and each census area, and the geometry of the census areas for each combination of stores and census areas.  So, if you have 5 stores and 1,000 census areas, you would have a table with 5,000 rows: Continue reading

# SQL and ogr to create 4D lines

A friend recently asked me to help him generate polyline shapefiles with Z and M values that he could deliver to a customer.  The problem was, the software he was using supported the import of Z and M values, but did not support the export of those files.  The other problem was, he has zillions of data tables that he needed to export!

Fortunately, PostGIS allows us to create Polyline ZM data rather easily.  The next part was to figure out how to get it exported to a shapefile.  So, here goes:

Assume you have a table with 4 columns: lineID, Z, M, and geometry.  The geometry field is a point feature that represent vertices on a line.  The lineID separates all those points by which line they are part of, and the Z and M values are, well, Z and M.

Make the Polyline ZM

The SQL command to create a 4D line is:

```SELECT  lineid,
ST_MakeLine(ST_MakePoint(ST_X(geometry),
ST_Y(geometry), z, m)
) AS g
FROM zpts
GROUP BY lineid;```

In this case, I am making a line (ST_MakeLine) as a series of X (ST_X), Y (ST_Y), Z, and M values.  Grouping the results of the query by the lineid allows me to create a line for each set of points. Continue reading