Posted on

Spatial SQL for the Geographer – Introduction

During WhereCamp Boston one of the impromptu break-out sessions was on spatial SQL. In WhereCamp fashion the person who proposed the session was looking to learn more about spatial SQL and its many uses.  With about 20 people in the session I volunteered to give a few examples of spatial SQL within Microsoft SQL Server.  Now, I was not prepared to give an in-depth or very informative session.  In fact, it was probably the worst presentation I have ever given.  I went through a number of examples of of some basic principals and I said I would put together a “Hello World” example of spatial SQL sometime after the meeting.

But, to complete a “Hello World” example we need to develop some background first.  In order to develop the guide I will be putting it together from the ground-up.  I use Microsoft SQL Server everyday, but I am GIS professional first, and many of the readers of this site are also GIS professionals.  Therefore, I will write the how-to from this point of view.  If you are a database professional this guide might be a little boring, but if you are a GIS professional I hope you’ll get something out of it.

The guide will be broken into three additional posts.  The first will be cover some background and vocab.  Part two will discuss basic spatial SQL syntax, and the third part will walk through how to expand basic scripts into larger sql processes. Once the guides have been finished I will provide links below.

Part 1 – Welcome to Spatial SQL – 11/14/2011

Part 2 – Basic Spatial SQL Scripts – 11/21/2011

Part 3 – More Basic SQL Scripts – 1/2012

Part 4 – Working Spatial SQL into Larger Processes – 2/2012

*Update – 12/4/2011- with the holidays and other commitments I am going to push back the last two installments!

This guide is being developed for use with Microsoft SQL Server.  That’s the DBMS that I use on a daily basis.

Before I release these posts feel free to browse some resources on spatial SQL and Microsoft SQL Server:

Posted on

I’m glad I don’t live in Connecticut (anymore)

With this past weekend’s major winter storm that rolled through parts of New England many communities are without power.  CL&P has released an outage map that is updated every 30 minutes.  This is a great tool…if you have power to view it.

Here is the latest outage map as of 10:30pm on 10/31/2011:

Click for Full Size

It’s amazing to see the number of towns that have no power…

Update – National Grid, a major electric provider for my current state of residence, Massachusetts, also has an online outage map up and running.  The outage mapping site from CL&P is much easier to use.

 

Posted on

WhereCamp Boston 2011 Review

WhereCamp Boston was this past weekend and it was pretty cool.  Even with troubles with the Red Line (typical) about 50 people were there on Saturday.  I was signed-up for both days but other last minute commitments pulled me away from Sunday’s portion of the weekend conference.

There were two keynote talks on Saturday that were both very interesting.

The morning keynote went to Jeffrey Warren from the Public Laboratory.  He talked about a number of projects that they have been working on including the really amazing balloon mapping techniques that they have developed.  I’ve seen him talk a couple times and after I immediately want to create my own aerial photography!

Tom MacWright from Development Seed gave the afternoon keynote talk and discussed a number of projects including MapBox.  The tools he demonstrated were pretty sweet.  I recommend that you take five minutes and check it out.

A few take-aways from my time at Boston’s first WhereCamp:

  • There was a good variety of people.  There was the GIS crowd, the programmers, the open sourcers, and the spatially enabled.  That mix led to some great conversations in the breakout sessions and in the common areas during the breaks.
  • Speaking of open source, there was a definite open source feel to the meeting, which was completely understandable, as many of the participants came from that side of the GIS coin.
  • I need to learn more about open source GIS.
  • There were some great sessions that emerged from the ideas of the group.  I went to a really good Google Android app builder session, and I heard some good talk about some of the other sessions that occurred including those on open source mapping.
  • The Microsoft NERD facility was awesome and it has one of the best views of Boston.
  • I was really impressed with where people came from.  There were attendees from the Boston region, all over New England, the east coast, and even some from California!
  • The spontaneity was great.  People would chat in the meeting areas during the breaks about a particular idea and it would become a session.  Also, people would propose sessions with the hope that someone could come in and show them something they didn’t know.  That’s what exactly happened to me.  Someone proposed an intro to spatial SQL session and I ended up putting together an (but not that great) impromptu workshop!
  • For next year I am going to come prepared with a few pre-canned workshops so that I can contribute more to the sessions.

Finally, Guido Stein and the ISpatialBoston team did a great job planning the event.  I think next year will be even better as more participants will have ideas going into the weekend and be willing to contribute more.

Again, a big thank you to the organizers.  You did a great job!  Thank you!

Posted on

WhereCamp Boston – This weekend!

It is an exciting time for the spatial crowd in Boston!  WhereCamp Boston is this weekend and you need to sign-up now!

WhereCamp is an “unconference“, meaning that the people who come are the ones who drive the sessions.  Attendees plan the sessions, workshops, and panels through the WhereCamp Wiki or during the conference itself.  On the registration page many people are stating their interest in open source GIS, web mapping, and “what’s next”.

If you are going (and you really should) consider participating in the wiki and post your session idea!  I’ve been working on a number of really interesting projects lately and I am hoping to bring some of my ideas and challenges to the unconference.  Here is what I am interested in learning more about this weekend:

  • How to scientifically validate VGI and developing tools and methods to do so
  • Learning more about open source GIS, and more specifically, the growing body of analytical tools that are both well built and scientifically strong
  • The art of the spatial index
  • Aggregating VGI from web sources
  • Big data
  • Web map design best practices

As a “traditional” GIS guy I am also really interested in how the “non-traditional” spatial folks view “spatial”.  I am interested to learn about what their needs and challenges are and share ideas with them.  I look forward to brainstorming ideas with those who may see spatial problems from a different perspective than myself.  Sometimes the best ideas to solve a problem may come from those who see things differently than you.  That’s why I am pumped for this weekend.

The event is being held at Microsoft NERD in Cambridge, located in the techiest tech neighborhood in America!  The locations is a short walk away from the Kendall T stop and don’t worry, you’ll be able to get there.

The organizers have worked hard to put this together and the least we can do is show up and make this event great!

Where: Microsoft NERD
When: October 29 and 30th
Who: Anyone spatial
Why: Every spatial nerd from the greater Boston region will be there!


View Larger Map

Posted on

Upcoming Boston Spatial Unconferences and Meet-ups

October is a busy time for spatial unconferences and meet-ups in the Boston area!

The folks at ISpatialBoston are hard at work organizing Boston’s first WhereCamp, which will be taking place October 29th and 30th at the Microsoft NERD office in Cambridge’s technology hub, Kendall Square .  WhereCamp is an “unconference“, meaning that the attendees develop sessions based on what they are interested in.  They have three keynote speakers lined-up and I’m sure there will be many interesting sessions that develop over the course of the weekend.  Registration is open and it’s not too expensive. If you are in the area and are interested in “spatial” check out WhereCamp Boston.  I’m looking forward to my first unconference.  I can imagine that there will be a lot of open-source and crowdsourcing talk.  I’m interested in finding out more on how to, if possible, validate crowd-sourced data in relation to the impacts of natural catastrophes.

The other upcoming spatial gathering is Esri’s Boston DevMeet-Up taking place on October 20th.  The location is TBD, but last year’s Boston meet-up had about 50 to 60 people show-up and there were a few good talks.  I even won an EDN license!  The Esri DevMeet-Ups bring together Esri geospatial developers in an informal setting to talk shop.  It is also a good time to meet other geospatial developers and pick their brains!

Unlike the weekend long WhereCamp, the Boston Esri DevMeet-Up is only for a couple hours on a weeknight where Esri provides the food and drinks.  I hope there are some good talks.  I’d be interested to see some code examples or talks about HTML5 integration, web-based spatial analytics, or interactive visualization methodologies for very large datasets.

Again, if you are in the area, check out these non-traditional spatial meetings.  I know there are a ton of geospatial folks who can take public transit to these events.  Hope to see all the locals there!

Posted on

The Query Layer Query

The Problem

I am a big fan of the query layer in ArcGIS 10.  I routinely create and manipulate spatial data that lives in the geography data type in SQL Server.  The query layer tool has opened up a whole new set of analysis and visualization possibilities for a number of large data sets I work with.  Overall, I am a big fan…

But, I’ve run into a problem.  Using the query layer tool I couldn’t connect to a number of SQL Server databases.  When I would try to connect to certain databases I would get this cryptic error.

What Does this Mean?

What is going on? Is this an actual error?  I am missing some font pack therefore displaying the characters I see on screen?

With this useless error I started to trouble shoot.  I knew I had SQL Server databases that worked correctly and some that generated this error.  The only difference between the working and non-working databases was the length of the database name.  As it turns out the length of the database file name that is allowed by the query layer is limited to 31 characters.  Anything greater than 31 characters will generate this error.  This is not listed in the query layer documentation.

What the Heck!

In my job I work with dozens and dozens of databases that have a prescribed naming convention.  Sometimes, these SQL Server database names can get long, but no where near the SQL Server character limit.  Within the Esri software family there are described limits to database names.  When using ArcSDE the name of the database is limited to 31 character, which is understandable.

However, if a user adds a SQL Server database to the “Add OLE DB Connection” tool in ArcCatalog the database name can be more than 31 characters, but if the user adds a SQL Server database with a name greater than 31 characters to the query layer tool it will error out.  What a bummer.

What’s Next

I posted this question in the Esri forums and the response I got from the Esri rep was accurate, but this limit is a pain to those want to use this tool.  In the online documentation for the query layer the text states “Query  layers allow both spatial and nonspatial information stored in a DBMS to be easily integrated into GIS projects within ArcMap.”   I agree with this statement 100%, unless my database name has more than 31 characters…

By limiting the length of the database name Esri is saying that I cannot take full advantage of the tools provided by SQL Server.  Also, if I can only use 31 characters, please document it in the query layer documentation.  Either this is a bug, an undocumented limitation that needs to be addressed, or it is documented somewhere and I can’t find it (which is a real possibility).  I am thinking that this problem is probably a 32 bit issue, in fact a couple of my coworkers are thinking the same thing.  I’m using a 32 bit software in a 64 bit world!

As a geospatial database admin (GeoDBA for short), I need to make a decision that will impact the workflow for my organization.  I have business requirements that limit me from renaming these business critical databases, but I have new business requirements that are greatly benefited by the query layer functionality.  Do I rename databases (and modify the tools that access this information) or do I wait for Esri to fix the problem? Moving forward I’ll try to limit the number of characters in my databases but I’m using a SQL Server database, not an SDE database. Why is Esri limiting what I can do with that piece of software?  Unfortunately, it may be faster for me to rebuild everything than it would be to get a solution from Redlands.

I’ll submit this problem to Esri tech support soon.

 

And…I am running ArcMap 10 with SP 2 using SQL Server 2008 R2 that is fully patched and updated.  The error described has been repeated on several machines within my organization.

Until next time GIS geeks!

Posted on

GIS Doctor Mailbag

This blog has been up and running for a while now and I have gotten a number of email questions, responses to blog posts, and search engine keyword questions.  Let’s answer a few of them!

How do geographers collect spatial data?

  • By many means.  There are the arm chair geographers (like me!) who collect, develop and analyze spatial data from a variety of published data sources and there are those geographers who go out into the field.  These rugged souls create and administer surveys, collect data in the field in all conditions, and develop scientific studies to derive their own data.  Depending on the nature of the study a geographer may use a mix of data collection methodologies.

How do I use more memory with ArcGIS 10?

  • I hear this question more and more, especially as more users have 64 bit machines and automatically assume that everything will be faster.  ArcGIS 10 will use all the memory available in a 32 bit environment, which is 4 gigabytes. However, not all the tools and processes in ArcGIS will use all the memory.  To work around this problem users can chunk their analysis to try to speed things up, or they can review their analysis and try to develop a set of processes that make the analysis smaller, therefore faster.  In the past I have split up large input data sets (20 million plus points) and used python and ArcGIS tools to have the analysis run effectively.

How much ArcGIS Server 10?

  • Not cheap.  If you want free check out MapServer.

How can I get Bing Maps in my GIS?

  • If you are not behind a firewall a user can click on the Add Data button in ArcGIS and select Add Base Map.  The Bing data should then be a base map option. Since my instance of ArcMap is behind a firewall I cannot add the Bing Service. This is a bummer, since I am a big fan of Bing Maps.

How can I add Google Maps into ArcMap?

  • There isn’t a quick tool to load Google Maps into ArcMap.  However, there are a few applications out there that allow a user to port in the imagery from Google into ArcMap.  One well known tool is provided by Arc2Earth.  Now, if the user is running ArcServer Esri provides a number of tools to publish data in the Google Maps interface.

How do I kill a geoprocess ArcGIS 10?

How do I speed up ArcGIS 10 on 64 bit machine?

  • Wait until Esri releases a 64 bit version of ArcGIS, or start using GIS software that is native 64 bit.

Is it possible to use embed a WMS data source into a Google Maps mash-up?  

  • Why, yes it is.  Check some examples here…

Is ArcGIS difficult to learn?

  • I’ve been in the field of GIS for close to 10 years and I’m learning something new about ArcGIS everyday. GIS is complicated and the theory behind it is deep.  Many want GIS software to be simple, but users need to understand the details to properly frame an analysis and understand the results.  If GIS were very easy I believe you would get many people, more so than now, making poor decision from poor analysis.

Why did Esri skip version numbers?

  • They did?

What is wrong with ArcGIS 10?

  • Unfortunately, plenty.  Check out the bug fixes for SP1 and SP2.

Why do ArcGIS tools take so long?

  • Another question I hear all the time,  “why is process XYZ so slow?”.  The answer is never as direct as the person asking the question wants it to be.  There are a number of reasons why a particular process or processes may be slow.  To try to understand the issue I always work through a series of questions.  First, what are the specs of the machine running the process?  What other processes are running on the machine?  What are the dimensions of the data being processed?  Are the data sets in the analysis in the same projection? How large is the data, both spatially and in memory?  How intensive is the process itself?  How detailed is the data being processed?  The point is that there isn’t always a smoking gun to solve all processing questions.  Sometimes the process will be slow.

OK, that is it for now.  If you have any additional questions drop me a line!  Until next time.

Posted on

2012 AAG Conference

It’s that time of year to start thinking about the annual AAG conference.  The 2012 meeting is being held at the end of February in cold, snowy, New York City.  If you are a member of the Association of American Geographers you have probably received the 215 million emails about the 2012 AAG conference.   The call for papers runs through the end of September and will more than likely be extended into mid-October (it happens every year).  So, get those abstracts ready, those posters planned, and panelists committed. The AAG meeting is coming!

If you are familiar with Northeastern US weather you’ll know that you’ll need to bring your boots, heavy coat, winter hat and gloves to the conference.   Now, since it is the off-season in NYC you’ll probably get good deals on airfare and hotels, but you’ll have to pay extra in baggage for that extra bag with all your winter gear.

New York Road Trip 2011-02-21-071

At least New York will look nice during the conference, or it could be a big, wet mess. Source: Flickr

Now, I haven’t decided if I am going to the AAG meeting this year.   I’ve been to a few conferences so far in the past couple months and I think I may look to spend some time in a warm weather location instead.

 

 

Posted on

The GIS of Hurricane Irene

Hurricane Irene is upon us here in Boston.  Thankfully, I prepared yesterday so I can spend some time this morning blogging during hurricane (or tropical storm)!  Also, I’d like to get this blog out before we lose power, since Boston is on the windy side of the storm.

Now, geographers and GIS pros are all over this storm.  Unlike the Tohoku earthquake and tsunami, which had a ton of reactionary GIS development, hurricanes provide the opportunity to develop datasets, applications, and analysis before the storm arrives.  There are a number of great applications and datasets that have been generated in the past four days, with many more to come over the next several days.

Tracking Applications: Everyone loves online maps

New York Times hurricane tracker – Great, clean application.

MSNBC hurricane tracker – Built by Stamen.  Great information, and a great look, but no data downloads.  Open the data!

Esri – The Worldwide Leader has a pretty nice mapping application (as they should) tracking the storm. They have a number of social media links, which will be great to view for damage and impact information after the storm.

CNN – Old school, like 1998.  CNN, get with it and build a better mapping application.

Yahoo and ABC are both displaying a mash-up of the storm that appears to be developed by the AP.  The application has a number of different tabs with information related to the storm.

GIS Data Sources: What you are really here for.

NOAA -A number of technical data sets are available and have been updated throughout the storm.  Click on a map and look for the “Download GIS Data” option.  Shapefiles are available and a number of Google based mash-ups are included.  The NOAA site may not be as flashy as others but the data available is very valuable.

Weather Underground – This site has gotten a lot of press this week as the storm has approached Megalopolis.  They have a number of tools and data sets available including a slick tracking mash-up, and number off data sets which are not necessarily GIS ready, but GIS-“able”.

Esri – Yes, Esri is on this storm.  Earlier this week they published a site with a number of data links to GIS Web Services, data providers, and scientific data sources.

FEMA data sources and data feeds.  FEMA is providing data from both internal and external sources.

CrisisCommons – A number of links to data sources related to the storm.  This source will grow as the storm passes.

Maps and data are already available on Geocommons.

After the storm there will be a number of data sets that become available through the Army Geospatial Center.

Global Disaster Alert and System– Hurricane Irene information is available here.

What’s next?  As the storm passes and people are able to survey the damage you will start to see impact analysis data sets, loss estimation maps, and analysis on the storm itself including better measurements of rainfall, windspeed and the track of the storm.  Once that data starts to roll out I’ll update the page with some more links to data.

Good luck, stay safe, and stay dry northeasterners! These storms are nothing to mess with. Take them seriously!