Spatial Data, Part 5: Resources

This is part five in a series:

The things we’ve looked at over the last few lessons come from things that I’ve picked up from the people and places that I mention here.

Resources

1. Smart People

Much of what I’ve learned, I learned from these folks:

2. Recursion and Fractals

Simon Greener shows how to make a circle that’s made of circles, with text showing their position. The whole Spatial DB Advisor is filled with amazing tricks.

Alastair Aitchison shows have to draw recursive triangles.

Slava Murygin shows how to make recursive snowflakes.

3. Artwork

Alex Whittles shows how to convert a vector image into geospatial data.

Click these images to see how to make Grinch, a Christmas tree with proper coloring, one with random ornaments, Venus, or a hotel floorplan.

4. Utilities

SQL Magazine offers a quite of utilities to handle a variety of graphic and charting situations.

Download here.

5. Color Palette

The default color scheme that SQL uses doesn’t always fit the bill. But by specifying the order of the items you display, you can control which one gets which color.

;with n(x) as (select 0 union all select x+1 from n where x < 99)
select cast(x as varchar) label,
geometry::Point(x%10,x/10,0).STBuffer(.5)
from n order by x

The palette ends up looking like this.

6. Finding Geospatial Data

There are many collections of geospatial data available for free (and many more for pay). Being a cheapskate myself, I tend to stick with free as much as possible.

Search for terms like shapefile, geospatial, data, and the region (Kentucky, etc.) and the type of data (geography, roads, elevation, demographics, roads, etc.) that you’re interested in.

I generally prefer to work with .SHP shapefiles, but there are a number of filetypes out there. KML (Keyhole Markup Language) is another popular one.

When I get specific local data including buildings (like I did for MTSU in Part 2), I prefer to use the service at bbbike.org, but MapSys gives an overview of the various ways to find and import geospatial data.

7. Loading Geospatial Data

The tool I prefer for load the data is Shape2SQL from SharpGIS. It’s lightweight and easy to use, and the instructions should handle whatever troubles you might run into.

The one thing to keep in mind is that if you’re loading geographical data, you’ll need to switch the datatype and pick and SRID (usually the default of 4326).

8. A Parting Gift

For a little fun, run this select:

select convert(geometry,'multipolygon (((0.0193705 0.678262, 0.0871671 0.678262, 0.164649 0.290852, 0.251816 0.629835, 0.329298 0.629835, 0.416465 0.281167, 0.493947 0.678262, 0.552058 0.678262, 0.455206 0.194, 0.377724 0.194, 0.290557 0.562039, 0.184019 0.194, 0.106538 0.194, 0.0193705 0.678262), (0.59037 0.678262, 0.658167 0.678262, 0.735649 0.290852, 0.822816 0.629835, 0.900298 0.629835, 0.987465 0.281167, 1.06495 0.678262, 1.12306 0.678262, 1.02621 0.194, 0.948724 0.194, 0.861557 0.562039, 0.755019 0.194, 0.677538 0.194, 0.59037 0.678262), (1.16137 0.678262, 1.22917 0.678262, 1.30665 0.290852, 1.39382 0.629835, 1.4713 0.629835, 1.55847 0.281167, 1.63595 0.678262, 1.69406 0.678262, 1.59721 0.194, 1.51972 0.194, 1.43256 0.562039, 1.32602 0.194, 1.24854 0.194, 1.16137 0.678262), (1.93576 0.329593, 2.06167 0.329593, 2.06167 0.203685, 1.93576 0.203685, 1.93576 0.329593), (2.70046 0.901022, 2.77795 0.901022, 2.77795 0.194, 2.70046 0.194, 2.70046 0.290852, 2.64235 0.223056, 2.59393 0.194, 2.56487 0.184315, 2.50676 0.184315, 2.46802 0.194, 2.42928 0.223056, 2.39054 0.271482, 2.36148 0.368334, 2.36148 0.465186, 2.38085 0.552354, 2.40991 0.610465, 2.45833 0.658891, 2.49707 0.678262, 2.52613 0.687947, 2.59393 0.687947, 2.64235 0.668576, 2.70046 0.62015, 2.70046 0.901022), (2.70046 0.562039, 2.70046 0.348964, 2.62298 0.271482, 2.58424 0.252111, 2.52613 0.252111, 2.48739 0.281167, 2.46802 0.310223, 2.44865 0.368334, 2.44865 0.484557, 2.46802 0.542668, 2.48739 0.581409, 2.51645 0.610465, 2.5455 0.629835, 2.60361 0.629835, 2.65204 0.60078, 2.70046 0.562039), (2.95185 0.658891, 3.01965 0.678262, 3.06807 0.687947, 3.17461 0.687947, 3.21335 0.678262, 3.25209 0.658891, 3.27146 0.629835, 3.29083 0.571724, 3.29083 0.271482, 3.30052 0.252111, 3.31989 0.242426, 3.35863 0.242426, 3.36832 0.203685, 3.32958 0.184315, 3.29083 0.184315, 3.25209 0.203685, 3.22304 0.252111, 3.1843 0.223056, 3.12619 0.194, 3.08745 0.184315, 3.02933 0.184315, 2.99059 0.194, 2.96154 0.21337, 2.93248 0.242426, 2.91311 0.290852, 2.91311 0.348964, 2.93248 0.39739, 2.98091 0.445816, 3.01965 0.465186, 3.08745 0.484557, 3.21335 0.484557, 3.21335 0.562039, 3.19398 0.60078, 3.14556 0.629835, 3.06807 0.629835, 3.00996 0.610465, 2.95185 0.581409, 2.95185 0.658891), (3.21335 0.436131, 3.12619 0.436131, 3.06807 0.41676, 3.02933 0.39739, 3.00028 0.348964, 3.00028 0.300538, 3.01965 0.271482, 3.05839 0.242426, 3.10682 0.242426, 3.16493 0.261797, 3.21335 0.300538, 3.21335 0.436131), (3.46474 0.678262, 3.55191 0.678262, 3.71656 0.281167, 3.88121 0.678262, 3.95869 0.678262, 3.74561 0.194, 3.66813 0.194, 3.46474 0.678262), (4.49579 0.41676, 4.15681 0.41676, 4.15681 0.368334, 4.17618 0.329593, 4.19555 0.300538, 4.24397 0.261797, 4.30208 0.242426, 4.37957 0.242426, 4.42799 0.252111, 4.49579 0.271482, 4.49579 0.21337, 4.42799 0.194, 4.36988 0.184315, 4.28271 0.184315, 4.23429 0.194, 4.16649 0.223056, 4.11806 0.261797, 4.08901 0.310223, 4.06964 0.378019, 4.06964 0.474872, 4.07932 0.523298, 4.09869 0.571724, 4.14712 0.629835, 4.19555 0.668576, 4.26334 0.687947, 4.34083 0.687947, 4.39894 0.668576, 4.43768 0.639521, 4.46673 0.60078, 4.4861 0.552354, 4.49579 0.494242, 4.49579 0.41676), (4.40862 0.474872, 4.15681 0.474872, 4.16649 0.523298, 4.18586 0.562039, 4.2246 0.610465, 4.26334 0.629835, 4.32145 0.629835, 4.3602 0.610465, 4.38925 0.581409, 4.40862 0.532983, 4.40862 0.474872), (4.62127 0.678262, 4.68906 0.678262, 4.68906 0.581409, 4.72781 0.639521, 4.76655 0.678262, 4.78592 0.687947, 4.83434 0.687947, 4.8634 0.658891, 4.88277 0.62015, 4.89245 0.581409, 4.92151 0.629835, 4.95057 0.668576, 4.98931 0.687947, 5.02805 0.687947, 5.0571 0.668576, 5.07647 0.639521, 5.08616 0.591094, 5.08616 0.194, 5.01836 0.194, 5.01836 0.591094, 4.98931 0.62015, 4.96994 0.62015, 4.9312 0.581409, 4.89245 0.513613, 4.89245 0.194, 4.82466 0.194, 4.82466 0.591094, 4.7956 0.62015, 4.77623 0.62015, 4.71812 0.562039, 4.68906 0.513613, 4.68906 0.194, 4.62127 0.194, 4.62127 0.678262), (5.23585 0.658891, 5.30365 0.678262, 5.35207 0.687947, 5.45861 0.687947, 5.49735 0.678262, 5.53609 0.658891, 5.55546 0.629835, 5.57483 0.571724, 5.57483 0.271482, 5.58452 0.252111, 5.60389 0.242426, 5.64263 0.242426, 5.65232 0.203685, 5.61358 0.184315, 5.57483 0.184315, 5.53609 0.203685, 5.50704 0.252111, 5.4683 0.223056, 5.41019 0.194, 5.37145 0.184315, 5.31333 0.184315, 5.27459 0.194, 5.24554 0.21337, 5.21648 0.242426, 5.19711 0.290852, 5.19711 0.348964, 5.21648 0.39739, 5.26491 0.445816, 5.30365 0.465186, 5.37145 0.484557, 5.49735 0.484557, 5.49735 0.562039, 5.47798 0.60078, 5.42956 0.629835, 5.35207 0.629835, 5.29396 0.610465, 5.23585 0.581409, 5.23585 0.658891), (5.49735 0.436131, 5.41019 0.436131, 5.35207 0.41676, 5.31333 0.39739, 5.28428 0.348964, 5.28428 0.300538, 5.30365 0.271482, 5.34239 0.242426, 5.39082 0.242426, 5.44893 0.261797, 5.49735 0.300538, 5.49735 0.436131), (5.9037 0.765429, 5.98119 0.765429, 5.98119 0.668576, 6.20395 0.668576, 6.20395 0.610465, 5.98119 0.610465, 5.98119 0.300538, 6.01024 0.261797, 6.06835 0.242426, 6.16521 0.242426, 6.21363 0.252111, 6.21363 0.194, 6.14583 0.184315, 6.02961 0.184315, 5.96182 0.203685, 5.93276 0.232741, 5.91339 0.261797, 5.9037 0.310223, 5.9037 0.610465, 5.7778 0.610465, 5.7778 0.668576, 5.9037 0.668576, 5.9037 0.765429), (6.4747 0.765429, 6.55219 0.765429, 6.55219 0.668576, 6.77495 0.668576, 6.77495 0.610465, 6.55219 0.610465, 6.55219 0.300538, 6.58124 0.261797, 6.63935 0.242426, 6.73621 0.242426, 6.78463 0.252111, 6.78463 0.194, 6.71683 0.184315, 6.60061 0.184315, 6.53282 0.203685, 6.50376 0.232741, 6.48439 0.261797, 6.4747 0.310223, 6.4747 0.610465, 6.3488 0.610465, 6.3488 0.668576, 6.4747 0.668576, 6.4747 0.765429), (7.16677 0.901022, 7.27331 0.901022, 7.27331 0.794484, 7.16677 0.794484, 7.16677 0.901022), (7.00212 0.678262, 7.26362 0.678262, 7.26362 0.194, 7.18614 0.194, 7.18614 0.62015, 7.00212 0.62015, 7.00212 0.678262), (7.51501 0.678262, 7.59249 0.678262, 7.59249 0.581409, 7.63123 0.629835, 7.67966 0.668576, 7.73777 0.687947, 7.79588 0.687947, 7.83462 0.678262, 7.87336 0.649206, 7.89273 0.610465, 7.90242 0.571724, 7.90242 0.194, 7.82494 0.194, 7.82494 0.552354, 7.80557 0.591094, 7.76683 0.610465, 7.7184 0.610465, 7.66997 0.591094, 7.59249 0.513613, 7.59249 0.194, 7.51501 0.194, 7.51501 0.678262), (8.42499 0.678262, 8.49279 0.678262, 8.49279 0.203685, 8.4831 0.145574, 8.46373 0.0971477, 8.43468 0.0584068, 8.39594 0.0293511, 8.34751 0.00998063, 8.29908 0.0002954, 8.21192 0.0002954, 8.16349 0.00998063, 8.11506 0.0196659, 8.11506 0.0874625, 8.18286 0.068092, 8.23129 0.0584068, 8.30877 0.0584068, 8.3572 0.0777772, 8.38625 0.106833, 8.41531 0.155259, 8.41531 0.300538, 8.37657 0.252111, 8.32814 0.21337, 8.27971 0.194, 8.21192 0.194, 8.16349 0.21337, 8.11506 0.252111, 8.08601 0.300538, 8.06664 0.368334, 8.06664 0.474872, 8.08601 0.552354, 8.12475 0.62015, 8.18286 0.668576, 8.24097 0.687947, 8.30877 0.687947, 8.37657 0.658891, 8.42499 0.62015, 8.42499 0.678262), (8.41531 0.562039, 8.41531 0.358649, 8.37657 0.310223, 8.33783 0.281167, 8.2894 0.261797, 8.25066 0.261797, 8.20223 0.290852, 8.17318 0.329593, 8.15381 0.39739, 8.15381 0.474872, 8.16349 0.523298, 8.19255 0.581409, 8.25066 0.629835, 8.31845 0.629835, 8.36688 0.60078, 8.41531 0.562039), (8.71512 0.901022, 8.98631 0.901022, 8.98631 0.194, 8.90883 0.194, 8.90883 0.84291, 8.71512 0.84291, 8.71512 0.901022), (9.17958 0.678262, 9.26675 0.678262, 9.4314 0.310223, 9.59605 0.678262, 9.66384 0.678262, 9.40234 0.0874625, 9.3636 0.0390363, 9.32486 0.0196659, 9.27644 0.00998063, 9.20864 0.00998063, 9.20864 0.068092, 9.28612 0.068092, 9.32486 0.0971477, 9.38297 0.21337, 9.17958 0.678262), (9.92976 0.329593, 10.0557 0.329593, 10.0557 0.203685, 9.92976 0.203685, 9.92976 0.329593), (10.37 0.678262, 10.4475 0.678262, 10.4475 0.581409, 10.4862 0.629835, 10.5347 0.668576, 10.5928 0.687947, 10.6509 0.687947, 10.6896 0.678262, 10.7284 0.649206, 10.7477 0.610465, 10.7574 0.571724, 10.7574 0.194, 10.6799 0.194, 10.6799 0.552354, 10.6606 0.591094, 10.6218 0.610465, 10.5734 0.610465, 10.525 0.591094, 10.4475 0.513613, 10.4475 0.194, 10.37 0.194, 10.37 0.678262), (11.3478 0.41676, 11.0088 0.41676, 11.0088 0.368334, 11.0282 0.329593, 11.0475 0.300538, 11.096 0.261797, 11.1541 0.242426, 11.2316 0.242426, 11.28 0.252111, 11.3478 0.271482, 11.3478 0.21337, 11.28 0.194, 11.2219 0.184315, 11.1347 0.184315, 11.0863 0.194, 11.0185 0.223056, 10.9701 0.261797, 10.941 0.310223, 10.9216 0.378019, 10.9216 0.474872, 10.9313 0.523298, 10.9507 0.571724, 10.9991 0.629835, 11.0475 0.668576, 11.1153 0.687947, 11.1928 0.687947, 11.2509 0.668576, 11.2897 0.639521, 11.3187 0.60078, 11.3381 0.552354, 11.3478 0.494242, 11.3478 0.41676), (11.2606 0.474872, 11.0088 0.474872, 11.0185 0.523298, 11.0379 0.562039, 11.076599999999999 0.610465, 11.1153 0.629835, 11.1735 0.629835, 11.2122 0.610465, 11.2413 0.581409, 11.2606 0.532983, 11.2606 0.474872), (11.6137 0.765429, 11.6912 0.765429, 11.6912 0.668576, 11.9139 0.668576, 11.9139 0.610465, 11.6912 0.610465, 11.6912 0.300538, 11.7202 0.261797, 11.7784 0.242426, 11.8752 0.242426, 11.9236 0.252111, 11.9236 0.194, 11.8558 0.184315, 11.7396 0.184315, 11.6718 0.203685, 11.6428 0.232741, 11.6234 0.261797, 11.6137 0.310223, 11.6137 0.610465, 11.4878 0.610465, 11.4878 0.668576, 11.6137 0.668576, 11.6137 0.765429)))',0)