Posted on

Spatial SQL Tools from Codeplex

When I wrote the Spatial SQL (or SQL Spatial, whatever you want to call it, I use both interchangeably for better or worse) blog posts I never mentioned a set of tools that are available which greatly expand the capabilities of the spatial analysis within SQL.

http://sqlspatialtools.codeplex.com/

If you are working with spatial data within SQL Server I encourage you to check this out.  I know this works in SQL Server 2008 R2, but I cannot vouch for its usefulness in SQL Server 2012, as I have not used it yet.  For a complete list of the functionality available on Codeplex click here.

Until next time, keep on geo-ing!

Posted on

Sunday Morning Geo-Fun

Why is it that the only time I have to blog is on Sunday mornings?  Here are a few quick geo-items that tickled my fancy from the previous week.

  • Check out the article, The New Cartographers: OpenStreetMap’s World Takeover, from Carl Franzen at Talking Points Memo.  The first two parts of this story have been tweeted a lot this last week and I can see why.  The article provides a fairly good overview of OSM, including some background on the project, the nuances of licensing OSM data, and adoption in the tech industry.  Part three of the article comes out on Sunday.  Makes me feel good about calling 2012 the year of OSM.
  • Years ago I used to pump out Google Map Mash-Ups on a regular basis, some of which were developed during my time at the Map and Geographic Center at the University of Connecticut.  Well, after nearly two years one of those mash-ups got some press!  Check out the article in the Atlantic, Pre-Sprawl Aerial Images:’Next Best Thing to a Time Machine‘. The article discusses the dual-map mash-up that I developed for the On the Line Project that is used to compare the drastic changes in Connecticut’s landscape using current and historical aerial photography.  Pretty cool.
  • The guys at Google’s NC data center, which just got the indoors street view treatment, definitely Rickrolled streetview (Check out the image on the screens, also, why didn’t they blur out Rick Astley’s face too?).
  • Brian Flood has been doing a lot of great things for the online mapping and spatial data communities for a while now.  This video and post on the MapBox blog is the latest example.  Using Arc2Earth Sync to integrate with MapBox and ArcGIS appears smooth and simple.  Awesome.  There is a lot of great work happening in “spatial” and it’s only going to make what we do as geo-professionals better.
  • Speaking of MapBox, when does Esri try to scoop them up (if they haven’t already), like they just did with GeoLoqi?
  • Avid Geo Boston had their October meet-up this past week.  The video is here. Since I am a horrible member and missed the meet-up for the third straight month I cannot comment on the talks, but I’m sure everyone had a good time.
  • Avid Geo will be hosting their wildly successful annual Ignite Spatial event on November 14th at the Center for Geographic Analysis at Harvard.  Tickets are available here, and they are currently looking for presenters.
  • Don’t forget to take the totally unscientific GISDoctor.com ArcGIS 10.1 survey!
  • Finally, I’ll be updating some pages on my site this week, including the blogs page and some of the mash-ups.
  • As always, follow me on twitter @GISDoctor, and hopefully I’ll blog more this week.  I have tons of ideas!

 

Posted on

Sunday Morning Geo-Fun

I use Weather Underground for the my weather needs, especially the handy-dandy web-mapping application for “Is it going to rain on my run?” weather report.  Just recently, I was checking the radar before heading out and noticed what appeared to be a technical malfunction, or the opening scenes to an epic movie.

What is happening?  Were there massive, freak storms happening throughout the eastern US?  No.

What you are probably seeing are radar blobs/radar blooms.  It took a couple minutes of Googling, but here is a 2007 article from AccuWeather.com explaining what you see on the map.  Apparently I observed a phenomena actually known as Ground Clutter.  I’m neither a radar or weather expert so I won’t comment on what is technically happening but it’s still pretty neat to see on a map.

Posted on

Whaddya think of 10.1?

ArcGIS 10.1 has been out for a few months now and I am curious as to what the GIS public thinks of the newest release.  I haven’t upgraded any of my Esri products to the newest release yet, and probably won’t for a while, but I want to gauge the public’s reaction to the most recent “dot” release.

If you have any other thoughts or comments on ArcGIS for Desktop (ArcMap), ArcGIS Online, Esri Maps for Excel, or anything else spatial leave a message!

I’ll post the results in a couple weeks.

Here is the awesome, non-scientific, one question survey.  Take it!

Create your free online surveys with SurveyMonkey, the world’s leading questionnaire tool.

Posted on

Avid Geo Meet-Up – Thursday, 8/16/2012!

Avid Geo will be holding it’s monthly meeting for August this coming Thursday at Dogpatch   Labs in Cambridge.  Unfortunately I can’t make this month’s meeting but it looks to another good one.  David Zwarg, from Azavea will be talking about PostGIS raster formats and supporting GDAL drivers.  I’ve seen David speak a couple times now and its always about some pretty interesting stuff.  So, if you are into PostGIS,  GDAL, and like “talking shop” with some people in the know then head over to Cambridge this Thursday!

Posted on

Using a Where Statement in a SQL Spatial Join

I had a question the other day about how to incorporate a where statement into a spatial join (STIntersects).  Unfortunately, the examples I had previously posted didn’t cover that topic.  Well, here is an example of querying two tables, using the geometry data type in each, along with a where statement, to find all results that meet the defined spatial conditions:

select PP.*, SP.*
from Populated_Places PP
inner join States_Provinces SP with(Index(geom_sidx))
on PP.geom.STIntersects(SP.geom) = 1
where SP.NAME_0 = 'United States of America' 
and SP.NAME_1 = 'Massachusetts'

Let’s breakdown this query:

  • The query starts by calling all the columns from two different tables, a table with point data called Populated_Places, and a table containing polygon data called State_Provinces.
  • The query then performs an inner join between the two tables, In the inner join I specifically call the spatial index from the States_Provinces table.  I call this index because I want to use it, and because query optimizer may skip it, as it does with many spatial indexes.
  • In the ON statement I set up the STIntersects statement between the point table and the polygon table, only returning the records that intersect.  This is done by setting the Boolean requirement at the end of the STIntersects statement to 1.  I also make sure to set the correct column in each table that contains the spatial  datatype, which in this case is called geom for both tables.
  • Now, here comes the where statement,which is about as vanilla as vanilla can be.  In the where statement I simply limit the polygons that are eligible for the query based on some defined values.

This type of query will also work with line or polygon features, as demonstrated in the following picture.  In this example I query all the roads that intersect a polygon defined in the where statement.  You will notice that I have the spatial results tab open to display the intersecting line features.

This is a pretty simple example, but if you have never worked in this medium it can be a little confusing at first.  Have any more spatial sql questions?  Let me know.  I need to keep sharp.

Posted on

You Stay Classy, San Diego

I just wrapped up a geo-packed week at the Esri UC and I’m about head back east.  Once I get settled back into my routine I’ll post my thoughts on the conference (Metalachi was awesome) and discuss some of the major themes and trends that were the buzz this past week (mobile, cloud, more mobile, more cloud). In the mean time check out my twitter, @GISDoctor, for what I was thinking during the conference.

Until then, more Metalachi!

Posted on

Sunday Geo-Notes

It’s summertime and I’m not blogging or twittering as much.  Typical.  But, it’s Sunday morning and before I head out to the garden I wanted to share these few items:

  • I’m doing a lot of geo-analysis using MSSQL Server lately and as I tweeted, the spatial index can be the key to a fast query.  However, the spatial index is sometimes tricky to understand.  Check out the “The Black Art Of Spatial Index Tuning In SQL Server” for a good overview of SQL’s spatial indexing.  For other spatial SQL inspiration check out Bob Beauchemin’s blog.
  • Speaking of analysis, the folks at Somerville’e ResiStat, who I am a fan of, did a nice geo-analysis, actually using some real statistics, not just what they saw from Google Earth, to debunk  a “study” of equating tree coverage to income in Somerville, MA.
  • There is an Avid Geo meet-up this coming Thursday (7/19).  If you are a Boston based geo-pro or geo-nerd you should check out the group!
  • The biggest geo-news of the week, GeoIQ being bought by Esri, gota lot of people talking.  Some positive, some not-so-positive.  I just hope the talented folks at GeoIQ are given room to do their own thing and bring positive innovations to Esri’s product line.  Sometimes when the little guy is bought out by the big guy their ideas and creativity may languish in the corporate culture.  I hope this doesn’t happen to them.
  • The Esri UC festivities start at the end of this coming week with the ed and business summit kicking things off.  I’ll be heading out with a few of my coworkers and meeting up with some old grad school friends for the full UC.  Like last year, I’ll be focusing on the analysis presentation tracks.  I hope to see something about speeding up large geo-analyses.  I’ll touch on improving performance in my talk, but I want to see what others are doing, as everyone now-a-days is using huge datasets (whatever happened to the sample?).

Until next time, check out my twitter feed @GISDoctor.  I almost have a 100 followers!!!!!!!!!  Only 11M+ more followers until I catch Ashton Kutcher…

Posted on

The First Law of Geography – Today’s Geo Inspiration

Ever now and then I look for inspiration when designing a model or writing some code to solve a geo-problem.  Recently, while searching for some geo-inspiration I came across one of my favorite papers.  If you have ever taken any type of  GIS or spatial analysis course you have probably heard some variation of the following phrase, commonly referred to as the First Law of Geography:

“Everything is related to everything else,
but near things are more related than distant things”

This idea, my friends, is what defines the field of geo-analysis.  From interpolation to distance decay, and spatial autocorrelation to gravity models, the idea that locations that are closer together are more related than those that are far apart provides the base for the field of geographic analysis.  Like any great quote, many may know it but few know it’s origins.  So, where did this phrase come from?  Here it is…

A Computer Movie Simulating Urban Growth in the Detroit Region
W. R. Tobler
Economic Geography
Vol. 46, Supplement: Proceedings. International Geographical Union. Commission on Quantitative Methods (Jun., 1970), pp. 234-240

Waldo Tobler is very well known geographer (he has his own Wikipedia page!) and if you have ever taken a geography or GIS course at some point the professor probably referred to his work, either directly or indirectly.  A copy of the paper is available here and a reply to the First Law of Geography can be found here.  If you are in the learning mood both are quick reads.