I’ll be updating the spatial blogs page soon.
With GISDoctor.com entering its second year in a blog format I have some plans for the site that will hopefully keep readers interested and bring in new readers. Here is what I am planning for 2012!
- Spatial Analysis – As a geographer by training, and professional geographer by occupation, I do a lot of spatial analysis and spatial statistics. I will be developing a series of spatial analysis and spatial statistics posts over the year. I will start with the basics and move into more complicated subjects. Like the Intro to Spatial SQL guides, I will include test data and examples. The tutorials will be targeted towards the geographer and GIS user, as that is my area of expertise. I really want to focus this site towards the technical GIS professional and technical geographer during 2012. Too many GIS blogs review the news (including this blog). I want GISDoctor.com to become a technical resource for people who have questions like I do.
- Spatial SQL – During January and February I will be adding some more posts on Spatial SQL and its use in geographical analysis. I will be adding another series of more technical Spatial SQL how-to guides later in the year.
- Online Mapping – I will hopefully add a few how-to guides on developing map-mash ups using a variety of APIs. I’m interested in learning more about Bing Maps as well as Open Layers and Map Server. I hope to get a few examples out sometime this spring.
- Software Reviews – In 2012 ArcGIS 10.1 will be released (Hooray! or shucks. It depends on your point of view). I’ll post a detailed review a few weeks after I get to use the software intensely. Also, when I get SQL Server 2012 (Denali) I’ll review the improved spatial components.
- Conference Reviews – I’ll make sure to review the 2012 Esri UC and any other conferences I make it to.
- Finally, when applicable to the readers of this site, I’ll post news stories and items that I find interesting.
I’m sure there will be a number of other topics that I write about. So make sure you subscribe to the feed to get the latest updates.
Thanks for being a reader. The site has done a lot better than I could have imagined and I hope 2012 goes just as well.
Happy GIS New Years!
Before we begin I would like to make it clear that learning all the capabilities and limitations of SQL Server and Spatial SQL cannot be covered in three short lessons. If you believe you might need some more training please visit the Microsoft SQL Transact reference guide.
What is Microsoft SQL Server?
Microsoft SQL Server is a relational database system that stores data and provides several mechanisms to retrieve stored data. One of the beauties of relational database systems is that they allow you to effectively store a variety of data sets and they are both scalable and securable, and in my experience, much more trustworthy than any flat file.
There are a number of good references out there explaining SQL Server in much more detail than I would attempt. I recommend that you check a couple of them out:
- Good explanation of Microsoft SQL Server
- Microsoft SQL Transact – Microsoft’s SQL language
- Simple introduction to SQL from W3
- Microsoft SQL Server tutorials from Bill Gates
Explanation of Spatial Data Types
As you probably know by now a column in a table or view can be assigned one of many different data types. Most are familiar with the traditional data types such as varchars, integers, bits, floats, or decimals. With SQL Server 2008 two new spatial data types were introduced. These data types support the storage of spatial data, and are broken up into two different types. Their characteristics are important to understand:
- Geometry: Primary characteristic – spatial data stored in a planar system.
- Geography: Primary characteristic – spatial data stored using the ellipsoid.
- Here is a great blog post that describes the spatial data types in more detail.
Step One: Getting Started
The user first needs to download some software and data to their local machine.
- Download and install Microsoft SQL Server 2008 R2 Express. Make sure you download the correct version for your machine.
- Download and install Shape2SQL
- Download the tutorial data from here
Downloading Microsoft SQL Server 2008 R2 may take a few minutes. The user needs to make sure that they read the download and installation instructions before they proceed. Also, when installing the software the users needs to pay particular attention to the installation instructions.
Note: This tutorial was completed on a 64 bit machine running Windows 7. I cannot guarantee that any of this will work on anything different.
Notes about the Data. The populated places, countries, and states and provinces data sets were downloaded from Natural Earth and the roads data set came from Data.gov. The data represent the three different vector data types, points, lines and polygons. They also provide a variety of query options to work with during for the tutorials. Also, I renamed the data from the original names so for clarity during the tutorials.
Once you download the data I recommend first viewing it in a GIS program to make sure you downloaded it correctly. Here the user will see the four data sets in Quantum:
Step Two: Create a Database
Now that the software has been downloaded the user can open Microsoft SQL Server Management Studio. To do this the user will select to Start–> All Programs–> Microsoft SQL Server 2008 R2–>SQL Server Management Studio. Since this will probably be the first time the user opens MSSQL it may take a couple minutes to configure and load.
Next, the user will be prompted to connect to a server. In this case the server is the local machine. In this example I connect to my local machine instance and use Windows Authentication. Unless you configured your SQL instance differently you will connect the same way. Once these options are selected click connect.
Now that the user is connected to the server they can create a new database. First, the user will expand the database instance. Then the user will right click on the database folder and select New Database from the menu.
The new database window will now open. Here the user can configure a number of settings before the database is created. In this tutorial we want to name the database Spatial_Database which is set in the Database Name text box. Another option I like to configure is the location of the data and log files. This is done by clicking the … button under the Path column. I recommend storing the database in a location that has plenty of room to grow, as the default location on the C drive may not have space to expand on your local machine.
Once the settings are all set the user will click OK to create the database. The user can refresh the Databases folder to see the new, empty database.
The user is the system admin on this sever and this database be default. This means the user has permissions to create databases, delete databases, create tables and load data into them. With these privileges already in place the admin user can load data into the database without any further configuration.
Step Three: Load Data into the Database using Shape2SQL
Using Shape2SQL is really easy. The user will first open Shape2SQL and add a shapefile that will be loaded into the database as an individual table. To do this the user will first navigate to shapefile stored on the local machine by clicking on the Select Button.
The next step is to select the server and database where the data will live. The user will click on the Configure Button to do this. In the Configure window the user enter the name of the server in the Server Name window. The user will then select Use Windows Authentication and then select the appropriate database. In this tutorial the database has been named Spatial_Database. Once this information has been configured the user will click OK.
Now, back in the uploader window the user has several additional options to configure before loading the data into the database. First, the user needs to select the data type. The user will select either the geometry or geography data type. In this tutorial we load the data as the geometry data, as it is more flexible, in terms of what it will accept, than the geography data type.
The second setting that needs to be set is the name of the geometry column. The default for this setting is ‘geom’, but the user can change this if desired. Remember, all the tutorials will use the ‘geom’ column.
The user will then make sure the Create Spatial Index option is selected. This will ensure that a spatial index is built, which will improve the performance of the table in SQL Server. Finally the user will set the name of the table in the Table Name window.
There are a few settings that do not need to be selected or altered. Since this tutorial is using the geometry data type the user does not need to set the SRID. The user can also change which columns are loaded into the table. By default all columns are taken from the shapefile and loaded into the table. If a user does not want certain columns they will simply uncheck the columns they don’t want. Once all the settings are selected the user will select Upload to Database. Again, I may use all the columns in future tutorials, so you are best off keeping all of them.
Once the user clicks Upload to Database the shapefile will be loaded into the database as a table and the spatial index will be created. Depending on the size of the shapefile and the power of the user’s machine it may take several minutes to load the table into the database.
The user will repeat these steps and load all four shapefiles into their database. Once that is completed the user can proceed to the next section of the tutorial.
Step Four: Perform a Basic Query
Now that the shapefiles have been loaded into SQL Server the user can start to perform some queries. Now, if the user does not have any SQL experience at all it may make sense to read through this tutorial from Microsoft.
The first example query is probably the most simple SQL query one could perform. In this first example I will use the dbo.States_Provinces table. The user will open SQL Server, connect to their server, and then click the New Query button. The user will then paste the following into the query window:
use spatial_database go select top 100 * from dbo.States_Provinces
The results will return the first 100 records from the dbo.States_Provinces table. In SQL Server the user will notice there is a Results tab and a Spatial results tab. The results tab will display the tabular data and the spatial results will display the spatial data. The Spatial results tab will only appear if there is spatial data returned in the query.
Step Five: Set a Where Statement to Query Specific Data
The user can narrow down what is selected by incorporating some basic SQL statements, such as a where statement. The basic premise of a where clause is to limit the data returned from a query. The user is setting a condition in regards to what they want returned. For example, let’s say we only want the return records from the States_Provinces table that are for the United States. If the user explores the table they will set that this information is stored in the Name_0 column. However, we are unsure of the exact syntax of “United States” so we can use a like operator in the where clause. For example:
use spatial_database go select * from dbo.States_Provinces where NAME_0 like '%United States%'
The user will see only those records that have ‘United States’ in the Name_0 column. If the user clicks on the spatial results tab they will see the spatial representation of those records.
Alright, that is it! We have completed the first part of this tutorial. The user has downloaded the appropriate software and data, installed the software, loaded a number of shapefiles using Shape2SQL into SQL Server, and have completed two basic queries.
The next part to this tutorial will cover some more Spatial SQL queries. I’ll go into more detail about some spatial operations and provide a number of examples. If there is anything the you want to see let me know!
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!
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.
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!
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.
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.
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”.
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.
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!
In case you missed it there was a great story in the New York Times on Tuesday in regards to the use of GIS in historical analysis. The article, “Digital Maps are Giving Scholars the Historical Lay of the Land” , by Patricia Cohen, discusses the evolution of the spatial humanities and historical GIS/geography, which are growing disciplines in the humanities at colleges and universities around the country.
The article provides a nice overview of how historians, archaeologists, and other non-geographers have embraced spatial analysis and GIS in their research. I think this is a great article on a trend in the humanities that has been growing for years. I remember as an undergrad ten years ago developing GIS tools to visualize historical settings. In grad school I routinely helped non-geographers develop spatial analysis methodologies and visualization techniques to process and analyze historical GIS data. Much of that work ended up in scholarly publications. The spatial component really gave the authors an edge over other papers at that time.
So, if you get a few minutes check the article out. Anytime that GIS gets mentioned in the New York Times is great for our field!
A couple of notes from the article:
- The author references www.gis.com. I’m sure the marketing department at Esri liked the link.
- The article links to David Rumsey’s site. If you are a map junkie like myself you will love this site. An amazing map collection. This site has really influenced the development of online map libraries around the world.
- I wonder if the growth of GIS and spatial analysis in the humanities (which has been happening for a number of years) has increased enrollment and/or developed programs in GIS and geography at schools where the spatial humanities are strong. The AAG should get on this.
- Does anyone remember the digital landscape history of Manhattan that was put together a couple of years ago? The project gained some press and buzz when it came out. I’m surprised this article didn’t mention it. Oh well…
Late last week Esri released their responses to user questions collected from the pre-conference survey. James Fee has a good response to a number of the questions that Esri posted. I particularly like his response to FGDC metadata support…
One question from the Esri User Conference Q&A caught my particular attention and it is something that I have been thinking about for a while now. The question, “Do you see Esri software becoming so easy to use that professional GIS people are edged out?” The folks at Esri answered that the GIS professional, a.k.a. the GIS guy, will be more important than ever because they will be the ones who connect a variety users to spatial data, applications, and analysis. I agree with that point, and I understand Esri’s goal of making their software and the concept of GIS as appealing to the masses as possible. Think about it, would you like to have a potential customer base of a couple hundred thousand, or a few million…
I believe the GIS guy isn’t going anywhere, in fact, if there is a greater demand for the use of GIS then there should be a greater need for GIS experts. However, to remain relevant in the workplace the GIS guy will need to become a well rounded technical GIS expert.
The profession is changing. No longer is the GIS guy just downloading data sets from a few GIS data warehouses, creating some metadata, doing some basic analysis, and creating a few nice maps for display. With a variety of web-based mapping and analysis sites and collaborative data analysis and collection sites anybody can collect, visualize, and share spatial data. And they do. From Google Maps to GeoCommons a growing number of individuals and organizations who are not trained in the classical arts of GIS are using tools and techniques that at one point were exclusively used by the GIS guy.
How does the GIS guy stay relevant in the office and not be replaced by someone who can develop a slick web-map using a data feed from Twitter? The GIS guy needs to become a technical expert in a number GIS related of fields they probably never had any training on in college (that’s another blog post). They need to demonstrate their value by having a solid set of technical and analytical skills as well as a flair data visualization.
The following is a list of skills that I believe that the GIS guy will need to not only be productive, but to stay relevant. I’ve thought about this list for a while. I base this list on my own experiences in searching for jobs in the past, being on GIS analyst hiring committees, reading GIS blogs, talking with friends and colleagues, and viewing GIS job posting. Here it is, in no particular order:
- The GIS guy needs to be well versed in relational databases. Data sets are getting larger and larger and the well worn methods of GIS data storage won’t cut it when you have millions of points, lines or polygons to analyze. Whether it is SQL Server, Oracle, PostGres, or SpatialLite, the GIS guy needs to understand the value of the relational database within GIS. This is especially true as relational databases become have increasing spatial capabilities and are easily connectable to both GIS and web-based tools.
- The GIS guy needs to be able to program – Python, C#, C++, SQL, pick your poison… No matter your daily workflow or GIS software you probably have a number of processes that either don’t exist as an out-of-the-box tool, or would greatly benefit from automation. Every, and I mean every, GIS professional has to be able to program. No excuses.
- The GIS guy needs to know geospatial analysis. I know this sounds a little silly at first but I have met a number of GISPs who couldn’t properly tell me the difference between a union and an intersect, what the Moran’s I measures, or how an IDW works. As the need for “geospatial” increases so will the need for advanced analysis. This is where the GIS guy can be of real value. GIS guys need to have training in the art of geospatial analysis and its applications in GIS.
- The GIS guy needs to know how to integrate web-based technologies into their GIS technology. The GIS guy has to be able to show value in their work and one of the quickest ways to do this is to share those valuable data sets that they have developed through web-based technologies. Whether it is a mash-up through Bing, Esri, Google, or OpenLayers the GIS guy needs to understand the benefits and challenges of developing tools in this framework.
- The GIS guy needs to be a GIS expert. This is related to a prior bullet. The GIS guy needs to understand raster and vector GIS analysis, proper data editing procedures, the differences between data formats, how projections impact analysis (do you know what a datum is?), the MAUP, and so much more. Why does the GIS guy need to know all of this? Well, if the GIS guy is working with geo-enabled individuals who may not be trained in the arts of GIS they have to be able to provide support for any question that may arise. If the GIS guy can’t or won’t provide this support then the their value and relevance will rapidly deteriorate.
- The GIS guy needs to continue to learn. I’ve heard “I didn’t have to do it in my job so I never learned it” one to many times from GIS guys. If you work with technology you need an evolving set of technical skills. The skill set I had five years ago isn’t the skill set I have today and the skill set I’ll have five years from now won’t be what I have today. Be proactive in your learning!
- The GIS guy needs to know that there is more to GIS than Esri. Go download Quantum, read up on ERDAS, learn about Cadcorp. The GIS industry is bigger than you think.
- And finally, the GIS guy needs to know how to make a web-map using a data feed from twitter…Know and understand the trends in the field and be able to communicate in the lingo of what is “next”.
By no means is this list complete, but if you are a GIS professional or an aspiring GIS professional this list might help you get ahead or stay ahead. Am I calling some GIS professionals out? Sure, but I do it because I don’t want to see a GIS professional lose out on a job to someone who has more technical training, but not geospatial skills. The set of skills I just described are attainable, and you don’t need to get a GIS certificate or have to go back to school to get them.
Do you think I’m crazy? Let me know. I’d be glad to discuss the list.
Taking a page from my friends at MAGIC and the Connecticut State Data Center, I’ve put together a simple dual-map Google Map mash-up of the recently released Massachusetts 2010 census data using the Google Map V3 API (see their example here). The release of the 2010 Massachusetts census did have its problems, but now the correct data are available for download. The census data are displayed using a dual-map approach. The top map displays the 2010 town-by-town population count while the bottom map displays the percent change between the 2000 and 2010 census.
Being a dual-map the zoom and pan controls are mimicked on each map. For example, if the user zooms or pans on the top map, the bottom map reacts accordingly. The user can also click on a polygon and a small info window will open that displays some basic census data. Over the past ten years population in Massachusetts has grown slowly, with increases in the towns of central Massachusetts. However, population has declined in many western Massachusetts towns.
*Update – I changed some of the code to position the maps and the legends so that they look better when the user shrinks the screen. The KMLs may also load slowly, depending on your connection speed.
I recently got Windows 7 on my work machine and while I was configuring my settings I came across a new feature that I thought was pretty neat. The user can now create a desktop background image slideshow. All the user has to do is point the tool to a directory of images, configure some settings, and presto, desktop slideshow.
Now, what does this have to do with this blog? Well, being a geographer who works with the natural environment, I wanted images that reflected my interests. I proceeded to download a number of images from NASA’s Image of the Day website for this purpose.
There are a number of images that are great for the type of analysis that I do, and I often use data from NASA and other related agencies in my analysis. There is an added bonus to these unique datasets, a number of the images come as GeoTiffs, or KMLs, making the data available in a number of GIS programs from Google Earth to Quantum (and ArcGIS). The images have a fairly good resolution are in an open format, making them available in almost every GIS software on the market.