PostGIS for Computer Vision Developers
Analyzing Geometric Objects Directly in SQL Without Additional Python Code
When we talk about the input and output of Computer Vision algorithms we mainly talk about two things — the images and the annotations. While the images are mostly stored in cloud storage, the annotations have a wider range of storing options. In my case, all the annotations are stored in a database.
I am a Python lover. Most of my work as a Computer Vision developer involves Python. But as I said, the annotations, which are an integral part of my data are stored in a PostgreSQL database. I used to write simple queries to extract all my data and then analyze it in Python using Pandas/OpenCV/Shapely or any other relevant library. And then one day, I was introduced to PostGIS.
In this post, I want to pass it forward and introduce PostGIS - an alternative to python, for analyzing geometric results (for PostgreSQL databases).
PostGIS
PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.
In our case, Computer Vision tasks, PostGIS allows geometric objects representation such as points, lines, and polygons. Using PostGIS’s geometric representation we can easily apply geometric manipulation and transformations directly in the SQL query, without additional Python code.
Some PostGIS methods are the equivalent of GeoPandas and Shapely in Python.
Data Overview
What I have in the database
- Data annotations — all of our image annotations are stored in the database. The annotation tool is connected directly to the database, and whenever an annotation job is done, all the data is written to the database in PostGIS format.
- Algorithmic results — algorithmic results, like segmentation, are also stored in the database. Whether its results from the production environment or its results from our research environment.
A connection between image annotations and the image algorithmic results can be done easily by joining according to image ID.
After applying a query for all the relevant data, we can easily manipulate it using PostGIS. For example, calculating the IoU for segmentation tasks. Using PostGIS we don’t need to write a Python script, the data can be analyzed directly in the query in a convenient way.
It can be even more useful when we want to add analytics layers and save the analyzed results back to the database. Instead of analyzing in Python which requires triggering a different process, and then writing to the database, it can all be done directly using SQL.
How it should be represented
- To geometry: In order to use PostGIS, all your objects (polygons, lines, points) should be represented in PostGIS geometry format.
# Point
SELECT ST_GeomFromGeoJSON(‘{“type”:”Point”,”coordinates”:[0,1]}’)# Line
SELECT ST_GeomFromGeoJSON('{"type":"LineString","coordinates":[[0,1], [15,35], [43.2,5.1]]}'))# Polygon
SELECT ST_GeomFromGeoJSON
(
'{
"type":"Polygon",
"coordinates":[[
[7.7, 51.8],
[3.8,48.3],
[7.2,43.5],
[18.6,43.8],
[17.9,50.2],
[13.7,54.0],
[7.7, 51.8]
]]
}'
)SELECT 'POLYGON((0 0, 0 1000012333334.34545678, 1.0000001 1, 1.0000001 0, 0 0))'::geometry
Other existing representations are Points, LineStrings, Polygons, MultiPoints, MultiLineStrings, MultiPolygons, and GeometryCollections.
Notice, as opposed to Python, when using a PostGIS Polygon representation, the first and last points must be the same in order to close the polygon and get a valid representation.
- From geometry: Geometry types can be converted back to arrays and text ST_AsText, ST_AsGeoJSON,
SELECT ST_AsGeoJSON('LINESTRING(1 2, 4 5)');
---------------------------
{"type":"LineString","coordinates":[[1,2],[4,5]]}SELECT ST_AsGeoJSON('LINESTRING(1 2, 4 5)')::json->>'coordinates';
---------------------------
[[1,2],[4,5]]=======================SELECT ST_AsText('POINT(111.1111111 1.1111111)'));
---------------------------
POINT(111.1111111 1.1111111)
PostGIS for Computer Vision
Segmentation — Intersection Over Union (IoU) metric
# Area
ST_Area(l.polygon)# Union
ST_Union(l1.polygon, l2.polygon)# Intersection
ST_Intersection(l1.polygon, l2.polygon)# IoU
ST_Area(ST_Intersection(l1.polygon, l2.polygon)) /
ST_Area(ST_Union(l1.polygon, l2.polygon))
Done!
Pose estimation — Percentage of Detected Joints (PDJ) metric
A detected joint is considered correct if the distance between the predicted and the true joint is within a certain fraction of the bounding box diagonal.
- ||d_pred — d_gt||² — the euclidian distance between ground truth keypoint and predicted keypoint
ST_Distance(ST_Point( GTi_x,GTi_y), ST_Point( predi_x,predi_y))- diagonal — diagonal distance of the bounding box
-- create bounding box
ST_MakeBox2D(ST_Point(x1,y1),ST_Point(x2,y2))-- get diaginal as line string
ST_BoundingDiagonal(ST_MakeBox2D(ST_Point(x1,y1),ST_Point(x2,y2)))-- calculate legnth
ST_Length(ST_BoundingDiagonal(ST_MakeBox2D(ST_Point(x1,y1),ST_Point(x2,y2))))
- threshold — the fraction of the diagonal.
Final query: for this example, threshold = 0.05.
ST_Distance(ST_Point( GTi_x,GTi_y), ST_Point( predi_x,predi_y)) < 0.05 * ST_Length(ST_BoundingDiagonal(ST_MakeBox2D(ST_Point(x1,y1),ST_Point(x2,y2))))Detection — bounding box labels from polygons
In my case, I don’t have bonding box annotations. I do have polygon annotations for the segmentation task. I can easily create detection annotations from the polygons using PostGIS.
- Option 1 — get upper left and bottom right points
SELECT Box2D('POLYGON((7.7 51.8,3.8 48.3,7.2 43.5,18.6 43.8,17.9 50.2,13.7 54,7.7 51.8))'::geometry)
----------------------------
BOX(3.8 43.5,18.6 54)- Option 2 — get all four points of the bounding box
SELECT ST_Envelope('POLYGON((7.7 51.8,3.8 48.3,7.2 43.5,18.6 43.8,17.9 50.2,13.7 54,7.7 51.8))'::geometry)SELECT ST_AsText(ST_Envelope('POLYGON((7.7 51.8,3.8 48.3,7.2 43.5,18.6 43.8,17.9 50.2,13.7 54,7.7 51.8))'::geometry))
----------------------------
POLYGON((3.8 43.5,3.8 54,18.6 54,18.6 43.5,3.8 43.5))
--- We have 5 coordinates and the last coordinate is always the first one as it is a polygon representation.
Polygon transformations
Do you want to rotate your polygon by 30 degrees? Do you want to scale it up by 2? Easy! PostGIS has several transformation options.
# Rotate 30 degrees counter-clockwise with origin x=50, y=160
SELECT ST_Rotate(l.polygon, pi()/6, 50, 160));# Scale X,Y
SELECT ST_Scale(l.polygon, 0.5, 0.75)# Scale X,Y,Z
SELECT ST_Scale(l.polygon, 0.5, 0.75, 0.8)# Translate X, Y
SELECT ST_Translate(l.polygon ,1, 0);
Polygon visualization
Want to show the user the great polygon you generated, but you don’t want to show all the hundreds of points? Use ST_Simplify!
ST_Simplify returns a “simplified” version, with fewer points, of the given geometry using the Douglas-Peucker algorithm.
SELECT ST_Simplify(geom,<tolerance>)More options
And there are many more options:
- Want to calculate the convex hull of a polygon? ST_ConvexHull
- Want to check whether polygons are overlapping? ST_Overlaps
- Check if a dot is inside a polygon? ST_Within
- Need to calculate the distance between geometries (polygons, lines, dots)? ST_Distance
- Check for self-intersections? ST_IsValid.
- Remove self-intersections?ST_MakeValid
If you insist on Python…
If I didn’t convince you that PostGIS is great, and you still want this functionality but in Python, take a look at:
Epilogue
I hope I convinced you that PostGIS is very a useful extension not only for data analysts but also for computer vision developers. It offers a rich variety of options and manipulations on geometries.
It helped me to upgrade my queries and spared me the overhead of using both SQL for extracting the data and Python for analyzing the data. Now I can do both in one place and directly save the results back to the database.
I hope it will help you too!
