I have a table with polygon geometry, in this case representing counties. I'm trying to figure out how to write a query that returns vertices sorted by the number of polygons that share that vertex. For example, think of the Four Corners ares of the United States where Arizona, Utah, Colorado, and New Mexico share a common corner.That corner would likely be at the top of the list, unless five states share a common vertex.

One thought I had, apply a 5' buffer to every vertex and return the polygons the buffer intersects. The logic seems like it would work but I suspect there is a better way.

I'm using PostgreSQL 9.5 Beta 1 with PostGIS 2.2.0 on Windows.

  • Write all of them into table, where each v. represented by str(x)_str(y) and find a frequency of unique x_y – FelixIP Oct 16 '15 at 2:21

You can do it in a one SQL statement in PostGIS. In the first step you decompose polygons to points. And in the second step you count the number of occurrences of points. It's quite fast query.

SELECT ST_GeomFromText(pt_geom) geometry, pt_geom, count(*) AS cnt FROM
 (SELECT kod,ST_AsText(ST_PointN(geom, generate_series(1, ST_NPoints(geom) -1))) AS pt_geom
   (SELECT kod, (ST_Dump(ST_Boundary(geom))).geom
    FROM country
    ) AS linestrings
  ) AS points
GROUP BY pt_geom order by count(*) DESC
WITH points as
 SELECT DISTINCT on (p,id) (ST_DumpPoints(geom)).geom p from the_table
SELECT count(*) number_of, p FROM points
ORDER BY number_of DESC
limit 100;

Some notes: The distinct part is there to remove duplicates and the repeated point in beginning and end of each polygon (untested, but I think it should work)

GROUP BY on a geometry will group on the bounding box, not the geometry. But that is ok with points.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.