I want to include only the portion of Alaska that's in the western hemisphere. Apparently, some islands are on the other side of the international dateline. I am using STIntersection to intersect the shapefile data I have for Alaska (stored in a geography column) and a text-specified polygon that includes only the western hemisphere. However, STIntersection is returning NULL. The documentation says that it will return NULL if the SRIDs don't match. I checked the SRIDs of both the shape data for Alaska and the text-specified polygon, and they both say 4326, as expected. I'm not sure what to do now. My guess is that I'm not specifying the polygon properly, though it does show up the Spatial Results tab just fine.
This is the SQL to generate the western hemisphere polygon:
SELECT geography::STPolyFromText('POLYGON((-180 0, -1 0, -1 90, -180 0))', 4326) AS s This appears in the spatial results window as this:
This is the SQL for Alaska. I have a table of shapefile data (from the US census bureau), which I can't easily reproduce here, so you'll have to take my word for it.
SELECT shape FROM shapefile_state WHERE enum_state = 2 The spatial results window shows this:
If I do a union of those two shapes,
SELECT geography::STPolyFromText('POLYGON((-180 0, -1 0, -1 90, -180 0))', 4326) AS s UNION ALL SELECT shape FROM shapefile_state WHERE enum_state = 2 I get this:
That implies that the polygon should be overlapping Alaska (specifically, the parts in the western hemisphere). And yet the NULL result from the following query:
SELECT geography::STPolyFromText('POLYGON((-180 0, -1 0, -1 90, -180 0))', 4326).STIntersection(shape) FROM shapefile_state WHERE enum_state = 2 Sanity check:
SELECT shape.STSrid, geography::STPolyFromText('POLYGON((-180 0, -1 0, -1 90, -180 0))', 4326).STSrid FROM shapefile_state WHERE enum_state = 2 This returns 4326 for both columns.
My knowledge of spatial data is not deep, so I am not sure how to debug this further.
أكثر...
This is the SQL to generate the western hemisphere polygon:
SELECT geography::STPolyFromText('POLYGON((-180 0, -1 0, -1 90, -180 0))', 4326) AS s This appears in the spatial results window as this:
This is the SQL for Alaska. I have a table of shapefile data (from the US census bureau), which I can't easily reproduce here, so you'll have to take my word for it.
SELECT shape FROM shapefile_state WHERE enum_state = 2 The spatial results window shows this:
If I do a union of those two shapes,
SELECT geography::STPolyFromText('POLYGON((-180 0, -1 0, -1 90, -180 0))', 4326) AS s UNION ALL SELECT shape FROM shapefile_state WHERE enum_state = 2 I get this:
That implies that the polygon should be overlapping Alaska (specifically, the parts in the western hemisphere). And yet the NULL result from the following query:
SELECT geography::STPolyFromText('POLYGON((-180 0, -1 0, -1 90, -180 0))', 4326).STIntersection(shape) FROM shapefile_state WHERE enum_state = 2 Sanity check:
SELECT shape.STSrid, geography::STPolyFromText('POLYGON((-180 0, -1 0, -1 90, -180 0))', 4326).STSrid FROM shapefile_state WHERE enum_state = 2 This returns 4326 for both columns.
My knowledge of spatial data is not deep, so I am not sure how to debug this further.
أكثر...