Background
A little known fact is that SCO has layers such as Wisconsin townships and county boundaries publicly available in the cloud via CARTO. This is true of most layers available in our web applications.
For a full list of layers SCO has public on CARTO click here. Note, metadata are limited on CARTO, so check out the dataset’s corresponding web application for metadata or contact us.
The Question
Recently we got a question about PLSS township ranges for each county:
Is there any resource that lists the PLSS ranges for Wisconsin counties? i.e. Dane County extends from [township #] N to [township #] N and from [range #] E to [range #] E.
Both Wisconsin counties and townships are publicly available and CARTO has a SQL API, so we can formulate an answer to that user’s question.
Building Our Query
Let’s start with the basics. CARTO, the company, provides software libraries to visualize spatial data on the web. One of the main services they provide is data storage that you can access using their libraries. This data is ultimately stored in a Postgres database. So if you are familiar with Postgres and SQL queries, you’re in good shape.
To start out very basic, we can query all fields in the county dataset to see what is there:
SELECT * FROM "sco-admin".scobase_wi_county_boundaries_24k WHERE false
"fields":{
"cartodb_id":{"type":"number","pgtype":"int4"},
"the_geom":{"type":"geometry","wkbtype":"Unknown","dims":2,"srid":4326},
"the_geom_webmercator":{"type":"geometry","wkbtype":"Unknown","dims":2,"srid":3857},
"county_nam":{"type":"string","pgtype":"text"},
"county_fip":{"type":"string","pgtype":"text"},
"county_cap":{"type":"string","pgtype":"text"}
}
We can do the same for the townships table to see what fields exist. Once we know the schemas of the datasets, we can start writing a query to get where the counties and townships intersect. And because CARTO uses Postgres with the spatial extension PostGIS, we can use ST_Intersects() to see where counties and townships intersect each other:
SELECT county_nam, min(twp) AS min_twp, max(twp) AS max_twp, min(rng) AS min_rng, max(rng) AS max_rng
FROM "sco-admin".scobase_wi_county_boundaries_24k AS counties
LEFT JOIN "sco-admin".scobase_wi_plss_townships_24k AS townships
ON ST_Intersects(counties.the_geom, townships.the_geom)
GROUP BY county_nam
But hold on, we didn’t account for west or east of the principal meridian which runs up close to the center of the state, splitting it in two.
We can modify the townships layer to report west ranges as negative using the following query:
SELECT dtr, CASE
WHEN dir=2 THEN rng*-1
ELSE rng
END AS rng, twp
FROM "sco-admin".scobase_wi_plss_townships_24k
Putting those two queries together by using our modified townships table as a subquery we get:
SELECT county_nam, min(twp) AS min_twp, max(twp) AS max_twp, min(rng) AS min_rng, max(rng) AS max_rng
FROM "sco-admin".scobase_wi_county_boundaries_24k AS counties
LEFT JOIN (
SELECT CASE WHEN dir=2 THEN rng*-1 ELSE rng END AS rng, twp, the_geom
FROM "sco-admin".scobase_wi_plss_townships_24k
) AS townships ON ST_Intersects(counties.the_geom, townships.the_geom)
GROUP BY county_nam
Using the CARTO SQL API
Next let’s work on constructing a URL to run our query. Take a look at the SQL API documentation from CARTO to see what all is possible and get some background. We’ll start with the base URL https://{username}.carto.com/api/v2/sql and fill it in with our information for SCO: https://sco-admin.carto.com/api/v2/sql.
Next, we’ll use the q= parameter to add our query. So if we paste our query in we get this:
https://sco-admin.carto.com/api/v2/sql?q=SELECT county_nam, max(twp) AS max_twp, min(twp) AS min_twp, max(rng) AS max_rng, min(rng) AS min_rng FROM "sco-admin".scobase_wi_county_boundaries_24k AS counties LEFT JOIN ( SELECT CASE WHEN dir=2 THEN rng*-1 ELSE rng END AS rng, twp, the_geom FROM "sco-admin".scobase_wi_plss_townships_24k ) AS townships ON ST_Intersects(counties.the_geom, townships.the_geom) GROUP BY county_nam
If you copy that query and paste it into your browser navigation (or click here) you’ll get this:
{"rows":[{"county_nam":"Adams","max_twp":21,"min_twp":13,"max_rng":8,"min_rng":4},{"county_nam":"Ashland","max_twp":53,"min_twp":40,"max_rng":2,"min_rng":-5},{"county_nam":"Barron","max_twp":37,"min_twp":31,"max_rng":-9,"min_rng":-15},{"county_nam":"Bayfield","max_twp":52,"min_twp":42,"max_rng":-3,"min_rng":-10},{"county_nam":"Brown","max_twp":26,"min_twp":20,"max_rng":23,"min_rng":18},{"county_nam":"Buffalo","max_twp":25,"min_twp":18,"max_rng":-9,"min_rng":-14},{"county_nam":"Burnett","max_twp":43,"min_twp":36,"max_rng":-13,"min_rng":-20},{"county_nam":"Calumet","max_twp":21,"min_twp":16,"max_rng":21,"min_rng":17},{"county_nam":"Chippewa","max_twp":33,"min_twp":27,"max_rng":-4,"min_rng":-11},{"county_nam":"Clark","max_twp":30,"min_twp":22,"max_rng":2,"min_rng":-5},{"county_nam":"Columbia","max_twp":14,"min_twp":9,"max_rng":13,"min_rng":6},{"county_nam":"Crawford","max_twp":11,"min_twp":6,"max_rng":-2,"min_rng":-7},{"county_nam":"Dane","max_twp":10,"min_twp":4,"max_rng":13,"min_rng":5},{"county_nam":"Dodge","max_twp":14,"min_twp":8,"max_rng":18,"min_rng":12},{"county_nam":"Door","max_twp":34,"min_twp":25,"max_rng":30,"min_rng":23},{"county_nam":"Douglas","max_twp":49,"min_twp":42,"max_rng":-9,"min_rng":-15},{"county_nam":"Dunn","max_twp":32,"min_twp":25,"max_rng":-10,"min_rng":-15},{"county_nam":"Eau Claire","max_twp":28,"min_twp":24,"max_rng":-4,"min_rng":-11},{"county_nam":"Florence","max_twp":41,"min_twp":37,"max_rng":20,"min_rng":14},{"county_nam":"Fond du Lac","max_twp":17,"min_twp":12,"max_rng":20,"min_rng":13},{"county_nam":"Forest","max_twp":42,"min_twp":33,"max_rng":17,"min_rng":11},{"county_nam":"Grant","max_twp":9,"min_twp":1,"max_rng":1,"min_rng":-7},{"county_nam":"Green","max_twp":5,"min_twp":1,"max_rng":10,"min_rng":5},{"county_nam":"Green Lake","max_twp":18,"min_twp":13,"max_rng":14,"min_rng":10},{"county_nam":"Iowa","max_twp":9,"min_twp":4,"max_rng":6,"min_rng":-1},{"county_nam":"Iron","max_twp":48,"min_twp":40,"max_rng":5,"min_rng":-2},{"county_nam":"Jackson","max_twp":25,"min_twp":18,"max_rng":2,"min_rng":-7},{"county_nam":"Jefferson","max_twp":9,"min_twp":4,"max_rng":17,"min_rng":12},{"county_nam":"Juneau","max_twp":21,"min_twp":13,"max_rng":6,"min_rng":1},{"county_nam":"Kenosha","max_twp":3,"min_twp":1,"max_rng":23,"min_rng":18},{"county_nam":"Kewaunee","max_twp":26,"min_twp":21,"max_rng":26,"min_rng":22},{"county_nam":"La Crosse","max_twp":19,"min_twp":14,"max_rng":-4,"min_rng":-9},{"county_nam":"Lafayette","max_twp":4,"min_twp":1,"max_rng":6,"min_rng":-1},{"county_nam":"Langlade","max_twp":35,"min_twp":29,"max_rng":15,"min_rng":8},{"county_nam":"Lincoln","max_twp":36,"min_twp":30,"max_rng":9,"min_rng":3},{"county_nam":"Manitowoc","max_twp":22,"min_twp":16,"max_rng":25,"min_rng":20},{"county_nam":"Marathon","max_twp":31,"min_twp":25,"max_rng":11,"min_rng":1},{"county_nam":"Marinette","max_twp":38,"min_twp":29,"max_rng":99,"min_rng":16},{"county_nam":"Marquette","max_twp":18,"min_twp":13,"max_rng":11,"min_rng":7},{"county_nam":"Menominee","max_twp":31,"min_twp":27,"max_rng":17,"min_rng":12},{"county_nam":"Milwaukee","max_twp":9,"min_twp":4,"max_rng":23,"min_rng":20},{"county_nam":"Monroe","max_twp":20,"min_twp":14,"max_rng":2,"min_rng":-5},{"county_nam":"Oconto","max_twp":34,"min_twp":25,"max_rng":99,"min_rng":14},{"county_nam":"Oneida","max_twp":40,"min_twp":34,"max_rng":12,"min_rng":3},{"county_nam":"Outagamie","max_twp":25,"min_twp":20,"max_rng":19,"min_rng":14},{"county_nam":"Ozaukee","max_twp":13,"min_twp":8,"max_rng":23,"min_rng":20},{"county_nam":"Pepin","max_twp":26,"min_twp":22,"max_rng":-10,"min_rng":-16},{"county_nam":"Pierce","max_twp":28,"min_twp":24,"max_rng":-14,"min_rng":-20},{"county_nam":"Polk","max_twp":38,"min_twp":31,"max_rng":-14,"min_rng":-20},{"county_nam":"Portage","max_twp":26,"min_twp":20,"max_rng":11,"min_rng":5},{"county_nam":"Price","max_twp":41,"min_twp":33,"max_rng":4,"min_rng":-3},{"county_nam":"Racine","max_twp":5,"min_twp":2,"max_rng":23,"min_rng":18},{"county_nam":"Richland","max_twp":13,"min_twp":8,"max_rng":3,"min_rng":-3},{"county_nam":"Rock","max_twp":5,"min_twp":1,"max_rng":15,"min_rng":9},{"county_nam":"Rusk","max_twp":37,"min_twp":32,"max_rng":-2,"min_rng":-10},{"county_nam":"Saint Croix","max_twp":32,"min_twp":27,"max_rng":-14,"min_rng":-20},{"county_nam":"Sauk","max_twp":14,"min_twp":8,"max_rng":8,"min_rng":1},{"county_nam":"Sawyer","max_twp":43,"min_twp":36,"max_rng":-2,"min_rng":-10},{"county_nam":"Shawano","max_twp":30,"min_twp":24,"max_rng":19,"min_rng":10},{"county_nam":"Sheboygan","max_twp":17,"min_twp":12,"max_rng":23,"min_rng":19},{"county_nam":"Taylor","max_twp":34,"min_twp":29,"max_rng":4,"min_rng":-5},{"county_nam":"Trempealeau","max_twp":25,"min_twp":17,"max_rng":-6,"min_rng":-10},{"county_nam":"Vernon","max_twp":15,"min_twp":11,"max_rng":2,"min_rng":-8},{"county_nam":"Vilas","max_twp":44,"min_twp":39,"max_rng":13,"min_rng":3},{"county_nam":"Walworth","max_twp":5,"min_twp":1,"max_rng":19,"min_rng":14},{"county_nam":"Washburn","max_twp":43,"min_twp":36,"max_rng":-9,"min_rng":-14},{"county_nam":"Washington","max_twp":13,"min_twp":8,"max_rng":21,"min_rng":17},{"county_nam":"Waukesha","max_twp":9,"min_twp":4,"max_rng":21,"min_rng":16},{"county_nam":"Waupaca","max_twp":26,"min_twp":20,"max_rng":16,"min_rng":10},{"county_nam":"Waushara","max_twp":21,"min_twp":17,"max_rng":14,"min_rng":7},{"county_nam":"Winnebago","max_twp":21,"min_twp":16,"max_rng":18,"min_rng":13},{"county_nam":"Wood","max_twp":26,"min_twp":20,"max_rng":7,"min_rng":1}],"time":0.309,"fields":{"county_nam":{"type":"string","pgtype":"text"},"max_twp":{"type":"number","pgtype":"int4"},"min_twp":{"type":"number","pgtype":"int4"},"max_rng":{"type":"number","pgtype":"int4"},"min_rng":{"type":"number","pgtype":"int4"}},"total_rows":72}
With no format specified we get a JSON format back. Super useful if using the result in a web application, but perhaps you would prefer to have a CSV like our user who asked the original question. The CARTO SQL API supports this using the format= parameter. In our case we want to add format=csv, so our final url will be:
https://sco-admin.carto.com/api/v2/sql?format=csv&q=SELECT county_nam, max(twp) AS max_twp, min(twp) AS min_twp, max(rng) AS max_rng, min(rng) AS min_rng FROM "sco-admin".scobase_wi_county_boundaries_24k AS counties LEFT JOIN ( SELECT CASE WHEN dir=2 THEN rng*-1 ELSE rng END AS rng, twp, the_geom FROM "sco-admin".scobase_wi_plss_townships_24k ) AS townships ON ST_Intersects(counties.the_geom, townships.the_geom) GROUP BY county_nam
Or click here to open the URL and download the CSV file.
Other than Marinette and Oconto county having max ranges of 99 due to ending on range 21 1/2 (weird case that may deserve its own post), it looks like our output is good to go.
Aside – Other Formats
As an aside, there are many useful formats (some spatial) including: GPKG, CSV, SHP, SVG, KML, SpatiaLite, GeoJSON.
So if we want a shapefile of this dataset we could change our format= option to shp and add the the_geom field to add the spatial information. That url would look something like this:
https://sco-admin.carto.com/api/v2/sql?format=shp&q=SELECT county_nam, min(twp) AS min_twp, max(twp) AS max_twp, min(rng) AS min_rng, max(rng) AS max_rng, counties.the_geom FROM "sco-admin".scobase_wi_county_boundaries_24k AS counties LEFT JOIN ( SELECT CASE WHEN dir=2 THEN rng*-1 ELSE rng END AS rng, twp, the_geom FROM "sco-admin".scobase_wi_plss_townships_24k ) AS townships ON ST_Intersects(counties.the_geom, townships.the_geom) GROUP BY county_nam, counties.the_geom
This will download a zip archive with a shapefile inside with the fields we calculated in the query.
Closing Remarks
This is a fairly technical post, but I enjoying sharing these types of tidbits of information. If you found this post particularly interesting or helpful and would like to see more content like this in the future, please get in touch to let us know.