{"id":5187,"date":"2010-02-10T22:01:45","date_gmt":"2010-02-11T03:01:45","guid":{"rendered":"http:\/\/scruss.com\/blog\/?p=5187"},"modified":"2010-02-16T21:02:04","modified_gmt":"2010-02-17T02:02:04","slug":"diary-of-a-geonumpty","status":"publish","type":"post","link":"https:\/\/scruss.com\/blog\/2010\/02\/10\/diary-of-a-geonumpty\/","title":{"rendered":"diary of a geonumpty"},"content":{"rendered":"<p>I have a GIS problem:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5188\" title=\"Map Image\" src=\"http:\/\/scruss.com\/wordpress\/wp-content\/uploads\/2010\/02\/Map-Image.png\" alt=\"\" width=\"518\" height=\"322\" srcset=\"https:\/\/scruss.com\/wordpress\/wp-content\/uploads\/2010\/02\/Map-Image.png 518w, https:\/\/scruss.com\/wordpress\/wp-content\/uploads\/2010\/02\/Map-Image-160x99.png 160w, https:\/\/scruss.com\/wordpress\/wp-content\/uploads\/2010\/02\/Map-Image-320x198.png 320w\" sizes=\"auto, (max-width: 518px) 100vw, 518px\" \/>There are two layers in the map above:<\/p>\n<ul>\n<li>the green areas represent land lots, with owners Arnold, Beal, Carr, &#8230;<\/li>\n<li>the red points represent wind turbines, or really anything that needs to be identified inside a lot.<\/li>\n<\/ul>\n<p>While I used to be quite decent with plain ol&#8217; SQL, the spatial extensions (such as are in <a href=\"http:\/\/www.gaia-gis.it\/spatialite\/index.html\">SpatiaLite<\/a>) are nipping my heid. What I need to query:<\/p>\n<ol>\n<li>How many turbines does each land owner get?<\/li>\n<li>Are there any turbines that aren&#8217;t in a lot?<\/li>\n<\/ol>\n<p>Here&#8217;s the data, and I&#8217;ll show you a solution once I work it out.<\/p>\n<ul>\n<li><a href=\"http:\/\/scruss.com\/wordpress\/wp-content\/uploads\/2010\/02\/count_points_in_polygons-shp.zip\">count_points_in_polygons &#8211; shape files<\/a><\/li>\n<li><a href=\"http:\/\/scruss.com\/wordpress\/wp-content\/uploads\/2010\/02\/count_points_in_polygons-sqlite.zip\">count_points_in_polygons &#8211; Spatialite<\/a><\/li>\n<\/ul>\n<p><strong>Update<\/strong>: dang, the <a href=\"http:\/\/www.gaia-gis.it\/spatialite\/spatialite-tutorial-2.3.1.html\">SpatiaLite tutorial<\/a> is useful. Here&#8217;s how to get the count for each owner:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">select Properties.owner, count(*) from Points, Properties where within(Points.geometry, Properties.geometry) group by Properties.owner;<\/pre>\n<p>which gives:<\/p>\n<table style=\"height: 89px;\" width=\"168\">\n<tbody>\n<tr>\n<th style=\"text-align: center;\">Owner<\/th>\n<th style=\"text-align: center;\">count(*)<\/th>\n<\/tr>\n<tr>\n<td>Arnold<\/td>\n<td style=\"text-align: right;\">1<\/td>\n<\/tr>\n<tr>\n<td>Beal<\/td>\n<td style=\"text-align: right;\">2<\/td>\n<\/tr>\n<tr>\n<td>Dockrill<\/td>\n<td style=\"text-align: right;\">2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Nifty, what!? Now to work out the other one, <span style=\"text-decoration: line-through;\">which I suspect will use a &#8216;not within()&#8217;.<\/span><\/p>\n<p><strong>Update #2<\/strong>: Nope, the <a href=\"http:\/\/groups.google.com\/group\/spatialite-users\/browse_thread\/thread\/61493931eb7ce510\">SpatiaLite mailing list<\/a> came through:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT id,\r\n       unitid\r\nFROM   points\r\nWHERE  id NOT IN (SELECT points.id\r\n                  FROM   points,\r\n                         properties\r\n                  WHERE  Within(points.geometry,properties.geometry));\r\n<\/pre>\n<p>I have to admit that I still don&#8217;t really think of nested SELECTs &#8211; my SQL formative years were spent on a DB that didn&#8217;t support them.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I have a GIS problem: There are two layers in the map above: the green areas represent land lots, with owners Arnold, Beal, Carr, &#8230; the red points represent wind turbines, or really anything that needs to be identified inside a lot. While I used to be quite decent with plain ol&#8217; SQL, the spatial [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[7],"tags":[509,2086],"class_list":["post-5187","post","type-post","status-publish","format-standard","hentry","category-computers-suck","tag-gis","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pQNZZ-1lF","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/posts\/5187","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/comments?post=5187"}],"version-history":[{"count":6,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/posts\/5187\/revisions"}],"predecessor-version":[{"id":5222,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/posts\/5187\/revisions\/5222"}],"wp:attachment":[{"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/media?parent=5187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/categories?post=5187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/scruss.com\/blog\/wp-json\/wp\/v2\/tags?post=5187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}