postgis-workshop/advanced_geometry_construction — Chinese (Simplified) @ OSGeo Weblate: See Also

Zen

	English	Chinese (Simplified)	Actions
	We can build on this result by joining it back to the ``nyc_subway_stations`` table to create a new table that has, for each route, a row for every station on that route.
	Now we can find the center point by collecting all the stations for each route into a single multi-point, and calculating the centroid of that multi-point.
	The center point of the collection of 'Q' train stops looks like this:
	So the northern most stop, the end point, appears to also be the stop furthest from the center. Let's calculate the furthest point for every route.
	We've added two sub-queries this time:
	stops_distance joins the centers points back to the stations table and calculates the distance between the stations and center for each route. The result is ordered such that the records come out in batches for each route, with the furthest station as the first record of the batch.
	first_stops filters the stops_distance output by only taking the first record for each distinct group. Because of the way we ordered stops_distance the first record is the furthest record, which means it's the station we want to use as our starting seed to build each subway route.
	Now we know every route, and we know (approximately) what station each route starts from: we're ready to generate the route lines!
	But first, we need to turn our recursive CTE expression into a function we can call with parameters.
	And now we are ready to go!
	Here's what our final table looks like visualized in QGIS:
	As usual, there are some problems with our simple understanding of the data:
	there are actually two 'S' (short distance "shuttle") trains, one in Manhattan and one in the Rockaways, and we join them together because they are both called 'S';
	the '4' train (and a few others) splits at the end of one line into two terminuses, so the "follow one line" assumption breaks and the result has a funny hook on the end.
	Hopefully this example has provided a taste of some of the complex data manipulations that are possible combining the advanced features of PostgreSQL and PostGIS.
	See Also
	`PostgreSQL Arrays <http://www.postgresql.org/docs/current/static/arrays.html>`_
	`PostgreSQL Array Functions <http://www.postgresql.org/docs/current/static/functions-array.html>`_
	`PostgreSQL Recursive Common TABLE Expressions <http://www.postgresql.org/docs/current/static/queries-with.html>`_
	`PostGIS ST_MakeLine <http://postgis.net/docs/ST_MakeLine.html>`_