地理交集操作正确语法及GEOG字段创建填充步骤咨询

Hey there! Let's break down how to tackle your homework steps and answer your question about geography intersection operations—all tailored to your two tables (brazosStreet and counties).

1. Adding the GEOG Column to Your Tables #

First, let's get that geography column set up for both tables:

• Right-click the table (start with brazosStreet, then repeat for counties) and select Design from the context menu.
• In the table design view, add a new column:
  • Column name: GEOG
  • Data type: Select geography from the dropdown list
• Save your changes (you might need to confirm if the table has existing data, but this is usually safe to proceed with).

2. Populating the GEOG Column from WKT Data #

Since your two tables use different WKT types (LINESTRING vs POLYGON), we'll use slightly adjusted UPDATE queries for each—both rely on converting WKT text to a geography object.

For brazosStreet (LINESTRING WKT) #

Use this query to convert the WKT column's line data into the GEOG column:

UPDATE brazosStreet
SET GEOG = geography::STGeomFromText(WKT, 4326)

Pro tip: The 4326 here refers to the WGS84 coordinate system (standard for GPS/经纬度 data). If your WKT uses a different spatial reference ID (SRID), replace this number with the correct one for your dataset.

For counties (POLYGON WKT) #

For polygon data, the conversion works the same way—SQL will automatically recognize the POLYGON WKT format. Here's the query:

UPDATE counties
SET GEOG = geography::STGeomFromText(WKT, 4326)

If you run into errors related to polygon ring orientation (SQL Server requires outer rings to be counter-clockwise), use the MakeValid() method to fix it:

UPDATE counties
SET GEOG = geography::STGeomFromText(WKT, 4326).MakeValid()

3. Correct Syntax for Geography Intersection Operations #

To check or retrieve intersections between your geography objects, use these built-in methods (assuming you're using SQL Server, which matches the geography type you're working with):

Check if Two Objects Intersect #

Use STIntersects() to find records where a street line crosses a county polygon. This returns 1 (true) if they intersect, 0 (false) otherwise:

SELECT
    s.StreetName, -- Replace with your actual street name column
    c.CountyName  -- Replace with your actual county name column
FROM brazosStreet s
JOIN counties c ON s.GEOG.STIntersects(c.GEOG) = 1

Retrieve the Actual Intersection Shape #

If you need the physical geometry of where the street and county overlap, use STIntersection():

SELECT
    s.StreetName,
    c.CountyName,
    s.GEOG.STIntersection(c.GEOG) AS IntersectionGeometry
FROM brazosStreet s
JOIN counties c ON s.GEOG.STIntersects(c.GEOG) = 1

内容的提问来源于stack exchange，提问作者FleshRocket