Radian can read ESRI geodatabases

radianI 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.

Workshops at the Maryland Geospatial Conference

tugisThe Maryland’s Geospatial Conference  () is on March 20/21, 2017.  I first attended TUgis in 1990, and it is always a great conference.  It is not too large, so it is  great way to have extended time with people.  So, if you had a technical question for someone from say ESRI, you could simply stop by their booth and have a chat.

This year I was asked to support the pre-conference workshops.  I will be presenting two workshops with the help of my students.  If you recall, my students are quite good at instructing others about GIS technology.  I’m really looking forward to the conference and interacting with people during the workshop.  Keep in mind, this is not something we are just throwing together – we’ve been spending a lot of time thinking about how to effectively move people through the material so that beginners do not get lost, and more technically savvy people are sufficiently challenged.  We are fanatical about making sure people’s learning experience is excellent.

A description of the courses are found here:

Spatial SQL: A Language for Geographers:  Are you stuck in a rut of only knowing how to use a GIS GUI? Do you want to learn how to automate tasks, but are afraid of computer programming. If so, SQL is the most powerful tool you can learn to help you perform complex GIS tasks. This hands-on course is designed to teach you how SQL can replicate many database and GIS tasks. We will start at a very basic overview and then proceed to more advanced topics related to GIS.

Topics to include:

  • Spatial is NOT Special
  • SQL Data Types
  • Traditional SQL
  • Spatial SQL for Vector and Raster Analysis
  • Spatial SQL for Classic Geographic Analysis

For this class, we’ll be using spatiaLite which is the spatial extension used with SQLite.  This is a great way to get started, as it is very similar to the functionality of Postgres/PostGIS.  If you want to move to enterprise GIS with Postgres or even Oracle or SQLServer, you’ll be in really good shape.

Python for Geospatial: If you are in the field of GIS, you’ve probably heard everyone talking about Python, whether it’s Arcpy in ArcGIS or special Python packages for doing things in open source.  In this hands-on workshop you will learn how Python is used to perform GIS analysis. The workshop will be an introduction to Python, with emphasis on integrating multiple Python plug-ins with ArcGIS and open source GIS.

Topics to include:

  • An overview of Python (variables, statements, I/O, writing code)
  • Python plug-ins for Geospatial (numpy, geocoder, pygal, Postgres)
  • A Taste of Arcpy
  • A Data Analytics Project with Python (for this, we will geocode addresses using Python, perform analysis with open source GIS, take the results into Arcpy to do more GIS analysis, compute statistical results with Python calling Excel, and then create charts and graphs of the results for use on the Internet—without ever opening up a single GIS product.)

If you want to learn more about how to use GIS technology, check out the 9 courses at gisadvisor.com.  

Undergraduate Geospatial Python Projects

earthballThis 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:

noahNoah 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.

jmJessica 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!


jtJohn 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.


jyJosh 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.   

Advanced Editing Capabilities with PostGIS

Today I want to introduce you to another one of my really great undergraduate students: Jeff Scarmazzi.  Jeff participated in the SUSRC conference that I posted about previously, but unfortunately, his photo and poster got deleted from my camera, so I never posted information about his project – I may eventually get around to posting Jeff’s poster, but for now it will have to wait.


Jeff is an exceptional student, and at this point can run circles around me with PostGIS, Leaflet, and Postgres – at the moment, I am still reserving the right to say that my spatial SQL is better than his, but that may not be true.  And, now that Jeff secured a prestigious internship with NASA over the summer, once the Fall rolls around it definitely won’t be true!

What I wanted to show you was a short presentation that Jeff gave on his work with Postgres and PostGIS in my Advanced GIS class.  For this project I had students create a geodatabase with ArcGIS that includes domains, subtypes, topology rules, etc.  Jeff decided to extend his work on this assignment and recreate the same kind of features using Postgres and PostGIS.  This presentation is just a small piece of what Jeff and his fellow students (Austin Barefoot, Carl Flint, and Jordan Roose) created, and focuses specifically on writing triggers and creating roles to automate and quality check digitizing procedures using Postgres.

The presentation is only about 15 minutes long, and you’ll be amazed to see what you can do with PostGIS to creating robust editing rules.

Warning Shameless plug: In case you wanted to learn how to implement PostGIS in an enterprise setting, check out my online course here.  Also, I have another course on how to write spatial SQL with PostGIS here.  

Open Source GIS Course at UMD – the Syllabus

UMD provided me with enrollment numbers, and we are getting a pretty full class.  However, a few people on the “outside” have asked for a little more depth into what will be covered.  So, I put together a quick outline on each of our class meetings.

Keep in mind, in addition to the class meetings, there will be weekly discussions and short assignments.  Each class session is 2.5 hours long.  Those sessions colored orange are classes that I will be on the campus live (please note that this is tentative, and the dates may slide around a bit), giving the lecture and assisting students in the lab.  The other class sessions are a combination of online video lectures or in laboratory exercises.  For those with full time jobs, most of the material can be performed at home on you own devices, although being in the lab with the TA will be helpful.

Again, please feel free to email  geog-gis@umd.edu to get more information about signing up for the class. Continue reading

k-Nearest Neighbor with PostGRES

Well, even though you didn’t ask for it, I decided to create a k-nearest neighbor analysis in PostGRES/PostGIS.  This has lots of potential: find the 5 nearest ATMs to every fast food restuarant; find the 3 closest medical clinics to each school; or find the 10 closest bars to each college.

Now, the LIMIT clause does allow us to find the k closest features to a particular feature.  But in this case, I’m not interested in a particular feature, I want the results for all features.

To do this in Postgres, I’m going to show you two things that I haven’t used until today: rank() and partition. Continue reading

k-nearest neighbor with SQL in Radian Studio

I wanted to give you another look at some features that Radian Studio will offer. I’ve shown how we can use SQL to replicate the ARC/INFO NEAR function, and how to perform Nearest Neighbor Analysis. But, another useful took is the ability to identify k-nearest neighbors. That is, rather than just identifying the nearest neighbor, you might want to identify the two, three, or k nearest neighbors.

Radian will allow that functionality by using the COLLECT aggregate clause. The COLLECT aggregate collects values from a subgroup into a table, returning a table with one or more fields.

it is like a SELECT which runs on a group. COLLECT takes a table and returns a table without requiring us to write a FROM section as we would with a SELECT. This is stuff that the real grown up databases like Oracle use, and Manifold is going to give it to us as part of Radian Studio.

SELECT park_no1,
SPLIT(COLLECT park_no2, dist
SELECT a.name AS park_no1, b.name AS park_no2,
GeomDistance(a.[geom (i)], b.[geom (i)], 0) AS dist
FROM [parks Table] AS a , [parks Table] AS b
WHERE a.name <> b.name
GROUP BY park_no1

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
INTO tradearea
  SELECT a.name, SUM(a.totpop) AS sumpop, ST_Collect(a.geometry) as g
     (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

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_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

Doing a GROUP BY in ArcGIS

As most of you know, I am a big fan of spatial SQL.  It is my go-to tool whenever working with GIS.  But, I have seen too many people using ArcGIS get tripped up with trying to summarize the results of a spatial operation because ArcGIS does not support SQL.  So today, to spare my ArcGIS friends the trouble of writing large “for” loops in Arcpy to populate data that takes hours to run, I want to show you two lines of Arcpy to very quickly replicate the GROUP BY function in SQL:

Using my favorite GIS data set in Tompkins County, NY, assume we have two layers: parcels2007 and watersheds:parwat

  Continue reading