{"id":3965,"date":"2025-03-25T07:36:48","date_gmt":"2025-03-25T07:36:48","guid":{"rendered":"https:\/\/www.go-inicio.com\/?p=3965"},"modified":"2025-03-26T09:09:06","modified_gmt":"2025-03-26T09:09:06","slug":"optimizing-geospatial-workflows-practical-postgis-tricks","status":"publish","type":"post","link":"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks","title":{"rendered":"Optimizing Geospatial Workflows: Practical PostGIS Tricks"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"3965\" class=\"elementor elementor-3965\">\n\t\t\t\t<div class=\"elementor-element elementor-element-79006fbc e-con-full e-flex e-con e-parent\" data-id=\"79006fbc\" data-element_type=\"container\">\n\t\t\t\t<div class=\"elementor-element elementor-element-dc51951 elementor-widget elementor-widget-text-editor\" data-id=\"dc51951\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p style=\"color: #71717a;font-family: TT-commons, sans-serif;font-weight: 400;letter-spacing: 0.5px\">At Inicio, we manipulate terabytes of geographical data, utilizing both worldwide datasets and detailed regional ones. We process, store, and analyze this data to identify the best possible solar project locations in Europe.<\/p><p style=\"color: #71717a;font-family: TT-commons, sans-serif;font-weight: 400;letter-spacing: 0.5px\">Most of the heavy lifting is handled by a PostGIS database\u2014an industry-standard, battle-tested solution for geospatial data manipulation.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-73b042e elementor-widget elementor-widget-image\" data-id=\"73b042e\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" src=\"https:\/\/www.osgeo.org\/wp-content\/uploads\/postgis-logo-1.png\" title=\"\" alt=\"The PostGIS logo, representing the text PostGIS and an elephant holding the earth on its trunk\" loading=\"lazy\" \/>\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-03cb75f elementor-widget elementor-widget-text-editor\" data-id=\"03cb75f\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p style=\"color: #71717a;font-family: TT-commons, sans-serif;font-weight: 400;letter-spacing: 0.5px\">When I started working with PostGIS, I encountered a bunch of hurdles along the way. I quickly realized that I was maintaining a mental list of tricks and patterns that I kept having to use over and over to solve my problems. Even now that I have some experience working with PostGIS, I keep coming back to my list, and reusing these tips. In this post, I\u2019d like to write up some of this knowledge, and describe how we solve these problems and how we make the most out of PostGIS at Inicio.<\/p><p style=\"color: #71717a;font-family: TT-commons, sans-serif;font-weight: 400;letter-spacing: 0.5px\">PostGIS can seem like a complex beast, and geospatial data processing is definitely difficult. You need to handle different geometry types, dimensions, spatial reference systems (SRS), geometry errors, indexes, and much more. Our geospatial engineering team had to develop a deep understanding of how PostGIS deals with geometries to be able to fully optimize our algorithm and enable Inicio to identify all the best solar projects in a country in a matter of hours.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-6598920 elementor-widget elementor-widget-heading\" data-id=\"6598920\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\"><h3>A short primer on geometries and their representations<\/h3><\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-abe3db1 elementor-widget elementor-widget-text-editor\" data-id=\"abe3db1\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p style=\"color: #71717a;font-family: TT-commons, sans-serif;font-weight: 400;letter-spacing: 0.5px\">Before we start, it\u2019s important that you get some understanding of the internal representation of geometries in a GIS system, and what types of geometries you may deal with.<\/p><p style=\"color: #71717a;font-family: TT-commons, sans-serif;font-weight: 400;letter-spacing: 0.5px\">The internal representation of a geometry always boils down to coordinates.<\/p><p style=\"color: #71717a;font-family: TT-commons, sans-serif;font-weight: 400;letter-spacing: 0.5px\">Put in simple terms: A\u00a0<code>Point<\/code>\u00a0is a single coordinate pair. A\u00a0<code>Line<\/code>\u00a0is a list of coordinates. A\u00a0<code>MultiLine<\/code>\u00a0is a list of\u00a0<code>Line<\/code>\u00a0s, a\u00a0<code>Polygon<\/code>\u00a0is a list of coordinates that forms a closed line, a\u00a0<code>MultiPolygon<\/code>\u00a0is a list of\u00a0<code>Polygon<\/code>s, you see the pattern.<\/p><p style=\"color: #71717a;font-family: TT-commons, sans-serif;font-weight: 400;letter-spacing: 0.5px\">There is an additional complexity that comes with polygons: they can have \u201choles\u201d, also called internal rings. Internal rings are extra polygons that are part of the main polygon and define internal holes (for example, see the\u00a0<code>Polygon<\/code>\u00a0below).<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-e645686 elementor-widget elementor-widget-image\" data-id=\"e645686\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img fetchpriority=\"high\" decoding=\"async\" width=\"800\" height=\"635\" src=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1.png\" class=\"attachment-large size-large wp-image-3915\" alt=\"Multiple types of polygons are shown on a single image. This includes an example of a line, a polyline, a point, a multipoint, a polygon and a multipolygon\" srcset=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1.png 976w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1-300x238.png 300w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1-768x610.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\">A bestiary of geometry types in PostGIS (and many other GIS tools)<\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-fa48cf5 elementor-widget elementor-widget-heading\" data-id=\"fa48cf5\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Dataset<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-0cec61b elementor-widget elementor-widget-text-editor\" data-id=\"0cec61b\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p style=\"color: #71717a;font-family: TT-commons, sans-serif;font-weight: 400;letter-spacing: 0.5px\">Throughout this post, I\u2019ll use some example geographical data to illustrate the operations I present.<\/p><p style=\"color: #71717a;font-family: TT-commons, sans-serif;font-weight: 400;letter-spacing: 0.5px\">Assume we want to process geometries representing local administrative units. For example, we could be tracking the population density of the country.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-7fe5c17 elementor-widget elementor-widget-spacer\" data-id=\"7fe5c17\" data-element_type=\"widget\" data-widget_type=\"spacer.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div class=\"elementor-spacer\">\n\t\t\t<div class=\"elementor-spacer-inner\"><\/div>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-2d84ae1 elementor-widget elementor-widget-html\" data-id=\"2d84ae1\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<details>\n    <summary>\u2139\ufe0f <b>Create the dataset yourself<\/b><\/summary>\n  <aside>\n      <p>  The query below will create the table we will do the work on. You can download a dataset of <a href=\"https:\/\/france-geojson.gregoiredavid.fr\/repo\/departements.geojson\">French departments in GeoJSON format<\/a> and follow along. \n      \n      <\/p>\n  <p>Just replace the <code>{\"type\":\"FeatureCollection\",\"features\":[...]}<\/code> in the query below for the content of the GeoJSON you downloaded.<\/p>\n        <small><pre><code class=\"language-sql\">create temporary table if not exists department_geoms(geometry) as (\n\twith json_features as (\n\t\tselect '{\"type\":\"FeatureCollection\",\"features\":[...]}'::json as collection\n\t)\n\tselect st_setsrid(st_geomfromgeojson(\n\t\tjson_array_elements_text(collection-&gt;'features')::json-&gt;'geometry'\n\t), 4326), json_array_elements_text(collection-&gt;'features')::json-&gt;'properties'-&gt;'code' as code from json_features\n)<\/code><\/pre><\/small>\n      \n  <\/aside>\n<\/details>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-64e102d elementor-widget elementor-widget-spacer\" data-id=\"64e102d\" data-element_type=\"widget\" data-widget_type=\"spacer.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div class=\"elementor-spacer\">\n\t\t\t<div class=\"elementor-spacer-inner\"><\/div>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-b414288 e-grid e-con-boxed e-con e-child\" data-id=\"b414288\" data-element_type=\"container\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-ba59680 elementor-widget elementor-widget-image\" data-id=\"ba59680\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"800\" height=\"719\" src=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/france-1-1024x920.png\" class=\"attachment-large size-large wp-image-3918\" alt=\"\" srcset=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/france-1-1024x920.png 1024w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/france-1-300x269.png 300w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/france-1-768x690.png 768w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/france-1.png 1494w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\">French departments (sub-regional administrative units)<\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-7288a8a elementor-widget elementor-widget-image\" data-id=\"7288a8a\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"758\" height=\"694\" src=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/finisterre-1.png\" class=\"attachment-large size-large wp-image-3921\" alt=\"\" srcset=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/finisterre-1.png 758w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/finisterre-1-300x275.png 300w\" sizes=\"(max-width: 758px) 100vw, 758px\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\">Finistere, a multipolygon area composed of several individual polygons (islands to the west and to the north)<\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-399b6c7 elementor-widget elementor-widget-image\" data-id=\"399b6c7\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" width=\"758\" height=\"694\" src=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/south-1.png\" class=\"attachment-large size-large wp-image-3924\" alt=\"\" srcset=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/south-1.png 758w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/south-1-300x275.png 300w\" sizes=\"(max-width: 758px) 100vw, 758px\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\">Pyr\u00e9n\u00e9es-Atlantique, an example department with internal rings, or \u201choles\u201d (visible to the right of the department shape).<\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-5d426f1 elementor-widget elementor-widget-heading\" data-id=\"5d426f1\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">(Measure and) Keep your geometric complexity low<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-ed4942c elementor-widget elementor-widget-text-editor\" data-id=\"ed4942c\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>In my opinion, this is the most crucial piece of advice in this article. As a data engineer, you need to know what data you are handling (&#8220;<em style=\"color: var(--e-global-color-text);font-family: var(--e-global-typography-text-font-family), Sans-serif;font-size: 18px;font-weight: var(--e-global-typography-text-font-weight);letter-spacing: var(--e-global-typography-text-letter-spacing);text-align: var(--text-align)\">what type?<\/em><span style=\"color: var(--e-global-color-text);font-family: var(--e-global-typography-text-font-family), Sans-serif;font-size: 18px;font-weight: var(--e-global-typography-text-font-weight);letter-spacing: var(--e-global-typography-text-letter-spacing);text-align: var(--text-align)\">&#8220;, &#8220;<\/span><em style=\"color: var(--e-global-color-text);font-family: var(--e-global-typography-text-font-family), Sans-serif;font-size: 18px;font-weight: var(--e-global-typography-text-font-weight);letter-spacing: var(--e-global-typography-text-letter-spacing);text-align: var(--text-align)\">how big is it?&#8221;, &#8220;should this relation be represented by foreign key to another table?&#8221;, &#8220;Am I sure this column will fit in a 32bit integer, even as we keep adding rows to the DB?<\/em><span style=\"color: var(--e-global-color-text);font-family: var(--e-global-typography-text-font-family), Sans-serif;font-size: 18px;font-weight: var(--e-global-typography-text-font-weight);letter-spacing: var(--e-global-typography-text-letter-spacing);text-align: var(--text-align)\">&#8220;).<\/span><\/p><p>Similarly, as a GIS data engineer, it&#8217;s essential to have a deep understanding of the nature and characteristics of your geospatial data. All of the above applies, but you also need to consider some additional factors specific to geospatial data.<\/p><p>\u00a0<\/p><p>My favorite query to quickly get an understanding of a table\u2019s complexity is the following:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-ca3d817 elementor-widget__width-initial elementor-widget elementor-widget-html\" data-id=\"ca3d817\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<pre class=\"sql\">\n    <code>\nwith gis_data_table as (select geometry as geom from department_geoms)\nselect\n    count(*) as num_rows,\n    concat(\n        round(\n            max(st_area(box2d(geom)))::numeric\n            \/ st_area(st_extent(geom))::numeric\n            * 100,\n            0\n        ),\n        ' %'\n    ) as max_size,\n    round(max(st_npoints(geom))::numeric, 0) as max_points,\n    round(max(st_nrings(geom))::numeric, 0) as max_rings,\n    max(st_numinteriorrings(st_geometryn(geom, 1))) as max_interior_rings,\n    concat(\n        round((avg((st_area(geom) \/ st_area(box2d(geom)))))::numeric * 100, 0), ' %'\n    ) as squared_geom_metric\nfrom gis_data_table\n<\/code>\n<\/pre>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-e40029c elementor-widget elementor-widget-text-editor\" data-id=\"e40029c\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p style=\"margin-bottom: 20px;padding-bottom: 0px;color: #71717a;font-family: TT-commons, sans-serif;font-size: 18px;font-weight: 400;line-height: var(--e-global-typography-text-line-height);letter-spacing: 0.5px;max-width: 900px;margin-left: auto;margin-right: auto;font-style: normal;text-align: start;text-indent: 0px;text-transform: none;background-color: #ffffff\">When we run this query on our department geometries table, we get:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-23f9333 elementor-widget elementor-widget-html\" data-id=\"23f9333\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<figure class=\"wp-block-table\">\n<table class=\"has-fixed-layout\">\n<thead>\n<tr>\n<th>num_rows<\/th>\n<th>max_size<\/th>\n<th>max_points<\/th>\n<th>max_rings<\/th>\n<th>max_interior_rings<\/th>\n<th>squared_geom_metric<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>96<\/td>\n<td>2 %<\/td>\n<td>582<\/td>\n<td>7<\/td>\n<td>2<\/td>\n<td>55 %<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<figcaption class=\"wp-element-caption\">Query results visualized in a table<\/figcaption><\/figure>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-06ed0bc elementor-widget elementor-widget-text-editor\" data-id=\"06ed0bc\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Let&#8217;s briefly go through the columns:\u00a0<\/p><ul><li class=\"whitespace-normal break-words\"><strong>num_rows<\/strong>: The total number of geometries in the table. So you can get a basic understanding of the dataset size.<\/li><li class=\"whitespace-normal break-words\"><strong>max_size<\/strong>: Is the ratio\u00a0 of the largest geometry&#8217;s bounding box area to the total extent of all geometries. This measures how much of the total space is occupied by the largest single geometry. Lower values indicate more evenly distributed geometries.<\/li><li class=\"whitespace-normal break-words\"><strong>max_points<\/strong>: Is the maximum number of points in any single geometry. This gives an upper bound on how detailed or complex your geometries are.<\/li><li class=\"whitespace-normal break-words\"><strong>max_rings<\/strong>: Is the maximum number of rings in any geometry. More rings mean more complex polygons and potentially more processing.<\/li><li class=\"whitespace-normal break-words\"><strong>max_interior_rings<\/strong>: Is the maximum number of interior rings (holes) in the first sub-geometry of each geometry. Similar to <code>max_rings<\/code>, more interior rings indicate more complex polygons.<\/li><li class=\"whitespace-normal break-words\"><strong>squared_geom_metric<\/strong>: Is the average ratio between the areas of your geometries geometry and their bounding box areas. Higher values indicate more &#8220;square-like&#8221; or rectangular geometries, which perform better with spatial indexes.<\/li><\/ul>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-74fe12f elementor-widget elementor-widget-text-editor\" data-id=\"74fe12f\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>\u00a0<\/p><p style=\"margin-bottom: 20px;padding-bottom: 0px;color: #71717a;font-family: TT-commons, sans-serif;font-size: 18px;font-weight: 400;line-height: var(--e-global-typography-text-line-height);letter-spacing: 0.5px;max-width: 900px;margin-left: auto;margin-right: auto;font-style: normal;text-align: start;text-indent: 0px;text-transform: none;background-color: #ffffff\">As a rule of thumb for performance critical tables, I try to keep the max_size below 10%, the number of rings and interior rings (max_rings and max_interior_rings) below 100, and the maximum number of points (max_points) below 256. The squareness metric should be as high as possible, while satisfying the other conditions.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-0d89ac7 elementor-widget elementor-widget-text-editor\" data-id=\"0d89ac7\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<blockquote><p><em>This is just a rule of thumb, which is based on our experience working with lots of geospatial data at Inicio, but YMMV!<\/em><\/p><\/blockquote>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-c680110 elementor-widget elementor-widget-text-editor\" data-id=\"c680110\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p><span style=\"color: var(--e-global-color-text);font-family: var(--e-global-typography-text-font-family), Sans-serif;font-weight: var(--e-global-typography-text-font-weight);letter-spacing: var(--e-global-typography-text-letter-spacing);text-align: var(--text-align)\">The results in the table are encouraging news about our table! While the maximum number of points is somewhat high due to the high precision of the administrative boundaries, the other metrics are within acceptable ranges.<\/span><\/p><div><p>Lower values for these metrics ensure better utilization of spatial indexes and keep computations manageable. The speed of geometric operations depends on these values, so tracking them is crucial for maintaining optimal performance. Of course, there is always a tradeoff, and you won\u2019t get all these metrics\u00a0 as low as you want without giving up on some other aspects of your data, like the precision or number of rows.<\/p><\/div><p>The rest of this article will give you tools and methods to understand these tradeoffs and how to make the right ones.\u00a0<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-70497f7 elementor-widget elementor-widget-heading\" data-id=\"70497f7\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Understand spatial indexes<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-64e4c4e elementor-widget elementor-widget-text-editor\" data-id=\"64e4c4e\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Spatial indexes is what makes PostGIS great. Similar to how normal database indexes work, the goal of a spatial index is to accelerate data lookups.<\/p><p><br \/>Creating a spatial index on your table is as simple as writing:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-1cb8136 elementor-widget__width-initial elementor-widget elementor-widget-html\" data-id=\"1cb8136\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<pre class=\"sql\">\n<code>\ncreate index gis_index on department_geoms using gist(geometry);\n\n<\/code>\n<\/pre>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-098be79 elementor-widget elementor-widget-text-editor\" data-id=\"098be79\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p style=\"color: #71717a;font-family: TT-commons, sans-serif;font-weight: 400;letter-spacing: 0.5px\">PostGIS spatial indexes are clever things. They are based on an algorithm called\u00a0<code>R-tree<\/code>. It works by grouping geometries together based on their bounding boxes in a recursive manner to build a spatial tree of bounding boxes. The leaves of that tree are the geometries.<\/p><p>For more information about how this algorithm works, see\u00a0<a href=\"https:\/\/dl.acm.org\/doi\/abs\/10.1145\/602259.602266\">the original research paper introducing R-trees<\/a>. Also\u00a0<a href=\"https:\/\/www.bartoszsypytkowski.com\/r-tree\/\">this blog post<\/a>\u00a0gives a good description of how it works.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-4ea16ea elementor-widget elementor-widget-image\" data-id=\"4ea16ea\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" width=\"554\" height=\"559\" src=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/rtree-1.png\" class=\"attachment-large size-large wp-image-3927\" alt=\"\" srcset=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/rtree-1.png 554w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/rtree-1-297x300.png 297w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/rtree-1-150x150.png 150w\" sizes=\"(max-width: 554px) 100vw, 554px\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\">An illustration of the spatial index. The bounding box of geometries (red) are hierarchically grouped (blue) into a tree, until we get to the top level (green)<\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-8271920 elementor-widget elementor-widget-text-editor\" data-id=\"8271920\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Checking that two bounding boxes intersect amounts to doing 4 value comparisons, which can be done on millions of rows in a heartbeat! Using this hierarchical organisation of geometries from their bounding boxes, PostGIS can super quickly narrow down a search to a few geometries by only doing bounding box operations. This often results in a tremendous speedup compared to sequentially scanning your table and checking every intersection.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-aac15ca elementor-widget elementor-widget-image\" data-id=\"aac15ca\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"615\" src=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/bbox-1.png\" class=\"attachment-large size-large wp-image-3930\" alt=\"\" srcset=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/bbox-1.png 1013w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/bbox-1-300x231.png 300w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/bbox-1-768x591.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\">A pathological example of a bounding box of a geometry covering a very large area.<\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-291201b elementor-widget elementor-widget-text-editor\" data-id=\"291201b\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>This means one important thing:<\/p><p>The closer your geometry is to a box (a rectangular geometry), the closer the operation you will be doing is to a simple index lookup. Therefore, the closest you are to a box, the faster the operation will be. Very long and narrow geometries can have a very large bounding box that will result in the index matching many objects that are actually far away from them. This is why it\u2019s important to keep\u00a0<code>squared_geom_metric<\/code>\u00a0high in the complexity metric above.<\/p><p>Let\u2019s visualize this with a simple intersection query. We have randomly generated geometries of different sizes spread out over the country. Let\u2019s call this new table <code>geometry_table<\/code>.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-c1e5490 elementor-widget elementor-widget-image\" data-id=\"c1e5490\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"747\" src=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/department-1.png\" class=\"attachment-large size-large wp-image-3933\" alt=\"\" srcset=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/department-1.png 831w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/department-1-300x280.png 300w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/department-1-768x717.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\">A dataset of random geometries with various sizes spread out over the whole territory of France<\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-8efcbbc elementor-widget elementor-widget-text-editor\" data-id=\"8efcbbc\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>We would like to know the department number of each geometry in\u00a0<code>geometry_table<\/code>. For this, we will need to join both tables using an intersection condition.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-c4e51f8 elementor-widget__width-initial elementor-widget elementor-widget-html\" data-id=\"c4e51f8\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<pre class=\"sql\">\n<code>\nselect department_geoms.code\nfrom geometry_table\njoin\n    department_geoms\n    on st_intersects(department_geoms.geometry, geometry_table.geometry)\n<\/code>\n<\/pre>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-e87b551 elementor-widget elementor-widget-text-editor\" data-id=\"e87b551\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Depending on your choice of index, you will get wildly different processing times for this very simple query.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-9bfcb40 elementor-widget elementor-widget-image\" data-id=\"9bfcb40\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"233\" src=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/Screenshot-2025-03-25-at-07.30.55-1024x298.png\" class=\"attachment-large size-large wp-image-3936\" alt=\"\" srcset=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/Screenshot-2025-03-25-at-07.30.55-1024x298.png 1024w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/Screenshot-2025-03-25-at-07.30.55-300x87.png 300w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/Screenshot-2025-03-25-at-07.30.55-768x223.png 768w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/Screenshot-2025-03-25-at-07.30.55-1536x447.png 1536w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/Screenshot-2025-03-25-at-07.30.55-2048x596.png 2048w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\">A comparison of database processing time for the query above<\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-d7a5110 elementor-widget elementor-widget-text-editor\" data-id=\"d7a5110\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p class=\"whitespace-pre-wrap break-words\">The graph illustrates the significant impact of indexing on a spatial join query performance.<\/p><p class=\"whitespace-pre-wrap break-words\">Without indexes, the query takes 2500ms to complete. Adding an index to the geometry table reduces this to 445ms, while indexing only the department table brings it down to 200ms. Interestingly, indexing both tables maintains the 200ms processing time without further improvement.<\/p><p class=\"whitespace-pre-wrap break-words\">This show that indexing choices can dramatically improve performance, but additional indexes don&#8217;t always provide benefits. In that case, this is because postgres is smart enough to know the <code>department_table<\/code> index is the best for this query. Understanding your specific data characteristics is always helpful to find the optimal index for geospatial queries.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-6e0008b elementor-widget elementor-widget-heading\" data-id=\"6e0008b\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Subdivide when you work with dense tables<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-1d81c7e elementor-widget elementor-widget-heading\" data-id=\"1d81c7e\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">The problem<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-1637316 elementor-widget elementor-widget-text-editor\" data-id=\"1637316\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>When doing a lookup in your PostGIS database, indexes are tremendously helpful, allowing you to quickly narrow down your search to a small subsets of objets that are close to the one you care about.<\/p><p>However, for very dense tables, with complex geometries, a spatial index is sometimes not enough. Some issues can also appear if you are comparing geometries of very different scales.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-edf55be elementor-widget elementor-widget-heading\" data-id=\"edf55be\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">st_subdivide<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-8a6105d elementor-widget elementor-widget-text-editor\" data-id=\"8a6105d\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>We have <code>st_subdivide<\/code> for that! This PostGIS function uses a geometry division algorithm to split geometries into components with less than a certain number of points.<\/p><p>With this function, a single geometry will be converted into several new smaller geometries. If you union them back, you will get the original geometry unchanged. This is an instance of the classic computational complexity vs. storage tradeoff.<\/p><p>Have a look at what happens to our complexity query as we vary the max number of points in our geometries:<\/p><figure class=\"wp-block-table\"><\/figure>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-9baa920 e-flex e-con-boxed e-con e-child\" data-id=\"9baa920\" data-element_type=\"container\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-f0c1361 elementor-widget elementor-widget-html\" data-id=\"f0c1361\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<figure>\n<table style=\"width: 100%\">\n<thead>\n<tr>\n<th>Value in \n<br><small><code>st_subdivide(geometry, {{value}})<\/code><\/small><\/th>\n<th><small>num_rows<\/small><\/th>\n<th><small>max_size<\/small><\/th>\n<th><small>max_points<\/small><\/th>\n<th><small>max_rings<\/small><\/th>\n<th><small>max_interior\n_rings<\/small><\/th>\n<th><small>squared_geom\n_metric<\/small><\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>512<\/td>\n<td>125<\/td>\n<td>2 %<\/td>\n<td>508<\/td>\n<td>3<\/td>\n<td>2<\/td>\n<td>54 %<\/td>\n<\/tr>\n<tr>\n<td>256<\/td>\n<td>245<\/td>\n<td>1 %<\/td>\n<td>250<\/td>\n<td>3<\/td>\n<td>2<\/td>\n<td>59 %<\/td>\n<\/tr>\n<tr>\n<td>64<\/td>\n<td>989<\/td>\n<td>0 %<\/td>\n<td>64<\/td>\n<td>3<\/td>\n<td>2<\/td>\n<td>68 %<\/td>\n<\/tr>\n<tr>\n<td>32<\/td>\n<td>2126<\/td>\n<td>0 %<\/td>\n<td>32<\/td>\n<td>2<\/td>\n<td>1<\/td>\n<td>71 %<\/td>\n<\/tr>\n<tr>\n<td>16<\/td>\n<td>3589<\/td>\n<td>0 %<\/td>\n<td>16<\/td>\n<td>2<\/td>\n<td>1<\/td>\n<td>79 %<\/td>\n<\/tr>\n<\/tbody>\n<caption>\nA comparison of complexity query values with various subdividing parameters.\n<\/caption>\n<\/table>\n<\/figure>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-a5d6865 elementor-widget elementor-widget-text-editor\" data-id=\"a5d6865\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p style=\"text-align: left\">By lowering the max number of points, we subdivide more, creating more rows and increasing our \u201csquareness\u201d. This can be very useful to increase the speed of an indexed join operation, since we now know that spatial indexes are better for geometries similar to squares.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-ee3e4dc elementor-widget elementor-widget-heading\" data-id=\"ee3e4dc\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">When there are too many points, simplify<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-d7c016e elementor-widget elementor-widget-heading\" data-id=\"d7c016e\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">The problem<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-de85ba3 elementor-widget elementor-widget-text-editor\" data-id=\"de85ba3\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Sometimes, you need your geometries to stay more or less the same, and cannot afford to subdivide them because you want them whole. But, if they are still composed of too many points for efficient processing, you\u2019ll need to find a way to make the geometries <em>simpler.<\/em><\/p><p>Here comes <code>st_simplify<\/code>, <a href=\"https:\/\/postgis.net\/docs\/ST_Simplify.html\">a PostGIS function that removes vertices from geometries<\/a>, keeping them the same up to a certain level of tolerance.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-058ce99 elementor-widget elementor-widget-heading\" data-id=\"058ce99\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">st_simplify<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-1552c5a elementor-widget elementor-widget-text-editor\" data-id=\"1552c5a\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Below, we compare the result of simplification with a tolerance of 1km and a tolerance of 10km. In the first case, we decrease the number of points significantly, while keeping the geometries similar visually (at least from a zoomed out view).<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-9853b72 elementor-widget elementor-widget-text-editor\" data-id=\"9853b72\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>On the other hand, while simplifying with a 10km tolerance brings the max number of points to an extremely low number, it also starts to degrade the shapes a lot.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-6d03697 e-grid e-con-boxed e-con e-child\" data-id=\"6d03697\" data-element_type=\"container\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t<div class=\"elementor-element elementor-element-842cccd e-con-full e-flex e-con e-child\" data-id=\"842cccd\" data-element_type=\"container\">\n\t\t\t\t<div class=\"elementor-element elementor-element-a2ad5c2 elementor-widget elementor-widget-html\" data-id=\"a2ad5c2\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<pre class=\"sql\"><code>\nselect \n    st_simplify( \n        st_transform(geometry, 2154), 1000 -- 1km \n    ) \nfrom department_geoms\n<\/code><\/pre>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-b68c99d elementor-widget elementor-widget-image\" data-id=\"b68c99d\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"621\" src=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/simp1-1.png\" class=\"attachment-large size-large wp-image-3939\" alt=\"\" srcset=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/simp1-1.png 1023w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/simp1-1-300x233.png 300w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/simp1-1-768x596.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\">Lightly simplified French departments - The geometries are still precise at this scale.<\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-067340f elementor-widget elementor-widget-html\" data-id=\"067340f\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<figure>\n<table style=\"width: 100%; table-layout: fixed;\">\n    <thead>\n<tr>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">num_rows<\/th>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">max_size<\/th>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">max_points<\/th>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">max_rings<\/th>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">max_interior_rings<\/th>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">squared_geom_metric<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>96<\/td>\n<td>2 %<\/td>\n<td>179<\/td>\n<td>5<\/td>\n<td>2<\/td>\n<td>45 %<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<figcaption>Complexity query results for light simplification<\/figcaption><\/figure>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<div class=\"elementor-element elementor-element-d94e56a e-con-full e-flex e-con e-child\" data-id=\"d94e56a\" data-element_type=\"container\">\n\t\t\t\t<div class=\"elementor-element elementor-element-e8014f7 elementor-widget elementor-widget-html\" data-id=\"e8014f7\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<pre class=\"sql\"><code>\nselect\n    st_simplify(\n        st_transform(geometry, 2154), 10000  -- 10km\n    )\nfrom department_geoms\n<\/code><\/pre>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-4df618f elementor-widget elementor-widget-image\" data-id=\"4df618f\" data-element_type=\"widget\" data-widget_type=\"image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\t<figure class=\"wp-caption\">\n\t\t\t\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"622\" src=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/simp2-1.png\" class=\"attachment-large size-large wp-image-3942\" alt=\"\" srcset=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/simp2-1.png 1023w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/simp2-1-300x233.png 300w, https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/simp2-1-768x598.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/>\t\t\t\t\t\t\t\t\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\">Aggressively simplified French departments - The geometries are very imprecise.<\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-948b6c6 elementor-widget elementor-widget-html\" data-id=\"948b6c6\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<figure>\n<table style=\"width: 100%; table-layout: fixed;\">\n    <thead>\n<tr>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">num_rows<\/th>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">max_size<\/th>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">max_points<\/th>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">max_rings<\/th>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">max_interior_rings<\/th>\n<th style=\"word-wrap: break-word; overflow-wrap: break-word; font-size: 0.9rem\">squared_geom_metric<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>96<\/td>\n<td>2 %<\/td>\n<td>19<\/td>\n<td>1<\/td>\n<td>0<\/td>\n<td>43 %<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<figcaption>Complexity query results for aggressive simplification<\/figcaption><\/figure>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-bf57b5b elementor-widget elementor-widget-text-editor\" data-id=\"bf57b5b\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>As you can see, this function allows you to reduce the value of max_points while keeping the values of the other metrics mostly unchanged. We also have no more geometries with &#8220;holes&#8221; (interior rings).<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-c779ae6 elementor-widget elementor-widget-heading\" data-id=\"c779ae6\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Learn to deal with various geometries\n<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-27792dc elementor-widget elementor-widget-text-editor\" data-id=\"27792dc\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Inputs are always messy. At Inicio, we deal with hundreds of data sources. Cleaning then up and making sure that the geometries we put in our database are how we expect them to be is crucial.<\/p><p>Here are a few additional functions we use a lot for making sure our geometries are of the right type and that they will behave the way we expect them to.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-faa421d elementor-widget elementor-widget-heading\" data-id=\"faa421d\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">st_collectionextract<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-05bb497 elementor-widget elementor-widget-text-editor\" data-id=\"05bb497\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p><code>st_collectionextract<\/code> is a PostGIS function that extracts the geometries of a specific type from a mixed collection of geometries.<br \/>For example, if you have <code>Linestring<\/code> and <code>Polygon<\/code> in your dataset but only want to &#8220;filter&#8221; the lines, this is the way to go. It is particularly useful when you have a table with many mixed types resulting from chained operations such as <code>st_intersection<\/code>, <code>st_union<\/code> and <code>st_difference<\/code>. This will create geometries of dimension 0, 1 and 2 (point, lines and polygons), and <code>st_collectionextract<\/code> can help you get just the ones you need.<\/p><p>See the <a href=\"https:\/\/postgis.net\/docs\/ST_CollectionExtract.html\">PostGIS documentation on <code>st_collectionextract<\/code><\/a> for more informations.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-2f1453c elementor-widget elementor-widget-heading\" data-id=\"2f1453c\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">st_multi<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-b28d33b elementor-widget elementor-widget-text-editor\" data-id=\"b28d33b\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p><code class=\"bg-text-200\/5 border border-0.5 border-border-300 text-danger-000 whitespace-pre-wrap rounded-[0.3rem] px-1 py-px text-[0.9rem]\">st_multi<\/code> is straightforward, and serves a similar purpose as the function above: making sure the geometries are of uniform type. Except that it does the opposite of extracting individual geometries. Points, lines and polygons have a &#8220;multi&#8221; variant containing collections of these geometries, and <code class=\"bg-text-200\/5 border border-0.5 border-border-300 text-danger-000 whitespace-pre-wrap rounded-[0.3rem] px-1 py-px text-[0.9rem]\">st_multi<\/code> converts input geometries into their multi counterpart.<\/p><p>It can sometimes be useful to ensure you only have &#8220;multi&#8221; types, especially when you&#8217;re exporting data.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-30401ee elementor-widget elementor-widget-heading\" data-id=\"30401ee\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h3 class=\"elementor-heading-title elementor-size-default\">st_force2d<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-7fa5c88 elementor-widget elementor-widget-text-editor\" data-id=\"7fa5c88\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>This function will ensure your geometry is 2-dimensional, meaning it won&#8217;t have an altitude &#8211; or z coordinate.<\/p><p>Again, if you are working with external, or user input, this function can act as a filter that ensures all your geometries are homogeneous.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-3beb933 elementor-widget-divider--view-line elementor-widget elementor-widget-divider\" data-id=\"3beb933\" data-element_type=\"widget\" data-widget_type=\"divider.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<div class=\"elementor-divider\">\n\t\t\t<span class=\"elementor-divider-separator\">\n\t\t\t\t\t\t<\/span>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-6861953 elementor-widget elementor-widget-heading\" data-id=\"6861953\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Conclusion<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-c59048f elementor-widget elementor-widget-text-editor\" data-id=\"c59048f\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>When working with geospatial data in PostGIS, the critical first step is always to understand the complexity of your dataset. Having precise control methods, such as <code>st_simplify<\/code> or <code>st_subdivide<\/code> to evaluate and manage this complexity, is extremely valuable. While PostGIS offers many functions and capabilities, establishing this basic understanding of your data&#8217;s structure and complexity is essential before applying more sophisticated techniques.<\/p><p>I hope this article will be useful to people working with PostGIS, and will encourage you to explore all the powerful functionalities of this tool for building awesome things with geospatial data.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>At Inicio, we manipulate terabytes of geographical data, utilizing both worldwide datasets and detailed regional ones. We process, store, and analyze this data to identify the best possible solar project locations in Europe. Most of the heavy lifting is handled by a PostGIS database\u2014an industry-standard, battle-tested solution for geospatial data manipulation. When I started working [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":3915,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[],"class_list":["post-3965","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tech-en"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Optimizing Geospatial Workflows: Practical PostGIS Tricks - Inicio<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Optimizing Geospatial Workflows: Practical PostGIS Tricks - Inicio\" \/>\n<meta property=\"og:description\" content=\"At Inicio, we manipulate terabytes of geographical data, utilizing both worldwide datasets and detailed regional ones. We process, store, and analyze this data to identify the best possible solar project locations in Europe. Most of the heavy lifting is handled by a PostGIS database\u2014an industry-standard, battle-tested solution for geospatial data manipulation. When I started working [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks\" \/>\n<meta property=\"og:site_name\" content=\"Inicio\" \/>\n<meta property=\"article:published_time\" content=\"2025-03-25T07:36:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-03-26T09:09:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"976\" \/>\n\t<meta property=\"og:image:height\" content=\"775\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Hugo Cisneros\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Hugo Cisneros\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"15 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks\",\"url\":\"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks\",\"name\":\"Optimizing Geospatial Workflows: Practical PostGIS Tricks - Inicio\",\"isPartOf\":{\"@id\":\"https:\/\/www.go-inicio.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1.png\",\"datePublished\":\"2025-03-25T07:36:48+00:00\",\"dateModified\":\"2025-03-26T09:09:06+00:00\",\"author\":{\"@id\":\"https:\/\/www.go-inicio.com\/#\/schema\/person\/db6543e3c7cd9ae98ee5ca24e158c03b\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks#primaryimage\",\"url\":\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1.png\",\"contentUrl\":\"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1.png\",\"width\":976,\"height\":775,\"caption\":\"Multiple types of polygons are shown on a single image. This includes an example of a line, a polyline, a point, a multipoint, a polygon and a multipolygon\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.go-inicio.com\/en\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Tech\",\"item\":\"https:\/\/www.go-inicio.com\/en\/category\/tech-en\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Optimizing Geospatial Workflows: Practical PostGIS Tricks\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.go-inicio.com\/#website\",\"url\":\"https:\/\/www.go-inicio.com\/\",\"name\":\"INICIO\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.go-inicio.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.go-inicio.com\/#\/schema\/person\/db6543e3c7cd9ae98ee5ca24e158c03b\",\"name\":\"Hugo Cisneros\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.go-inicio.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/b39fdb1d3585fca3736fd8ca5960dea8?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/b39fdb1d3585fca3736fd8ca5960dea8?s=96&d=mm&r=g\",\"caption\":\"Hugo Cisneros\"},\"sameAs\":[\"http:\/\/hugocisneros.com\"],\"url\":\"https:\/\/www.go-inicio.com\/en\/author\/cisnerosgo-inicio-com\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Optimizing Geospatial Workflows: Practical PostGIS Tricks - Inicio","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks","og_locale":"en_US","og_type":"article","og_title":"Optimizing Geospatial Workflows: Practical PostGIS Tricks - Inicio","og_description":"At Inicio, we manipulate terabytes of geographical data, utilizing both worldwide datasets and detailed regional ones. We process, store, and analyze this data to identify the best possible solar project locations in Europe. Most of the heavy lifting is handled by a PostGIS database\u2014an industry-standard, battle-tested solution for geospatial data manipulation. When I started working [&hellip;]","og_url":"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks","og_site_name":"Inicio","article_published_time":"2025-03-25T07:36:48+00:00","article_modified_time":"2025-03-26T09:09:06+00:00","og_image":[{"width":976,"height":775,"url":"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1.png","type":"image\/png"}],"author":"Hugo Cisneros","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Hugo Cisneros","Est. reading time":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks","url":"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks","name":"Optimizing Geospatial Workflows: Practical PostGIS Tricks - Inicio","isPartOf":{"@id":"https:\/\/www.go-inicio.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks#primaryimage"},"image":{"@id":"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks#primaryimage"},"thumbnailUrl":"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1.png","datePublished":"2025-03-25T07:36:48+00:00","dateModified":"2025-03-26T09:09:06+00:00","author":{"@id":"https:\/\/www.go-inicio.com\/#\/schema\/person\/db6543e3c7cd9ae98ee5ca24e158c03b"},"breadcrumb":{"@id":"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks#primaryimage","url":"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1.png","contentUrl":"https:\/\/www.go-inicio.com\/wp-content\/uploads\/2025\/03\/polys-1.png","width":976,"height":775,"caption":"Multiple types of polygons are shown on a single image. This includes an example of a line, a polyline, a point, a multipoint, a polygon and a multipolygon"},{"@type":"BreadcrumbList","@id":"https:\/\/www.go-inicio.com\/en\/optimizing-geospatial-workflows-practical-postgis-tricks#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.go-inicio.com\/en"},{"@type":"ListItem","position":2,"name":"Tech","item":"https:\/\/www.go-inicio.com\/en\/category\/tech-en"},{"@type":"ListItem","position":3,"name":"Optimizing Geospatial Workflows: Practical PostGIS Tricks"}]},{"@type":"WebSite","@id":"https:\/\/www.go-inicio.com\/#website","url":"https:\/\/www.go-inicio.com\/","name":"INICIO","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.go-inicio.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.go-inicio.com\/#\/schema\/person\/db6543e3c7cd9ae98ee5ca24e158c03b","name":"Hugo Cisneros","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.go-inicio.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/b39fdb1d3585fca3736fd8ca5960dea8?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b39fdb1d3585fca3736fd8ca5960dea8?s=96&d=mm&r=g","caption":"Hugo Cisneros"},"sameAs":["http:\/\/hugocisneros.com"],"url":"https:\/\/www.go-inicio.com\/en\/author\/cisnerosgo-inicio-com"}]}},"_links":{"self":[{"href":"https:\/\/www.go-inicio.com\/en\/wp-json\/wp\/v2\/posts\/3965"}],"collection":[{"href":"https:\/\/www.go-inicio.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.go-inicio.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.go-inicio.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.go-inicio.com\/en\/wp-json\/wp\/v2\/comments?post=3965"}],"version-history":[{"count":14,"href":"https:\/\/www.go-inicio.com\/en\/wp-json\/wp\/v2\/posts\/3965\/revisions"}],"predecessor-version":[{"id":4014,"href":"https:\/\/www.go-inicio.com\/en\/wp-json\/wp\/v2\/posts\/3965\/revisions\/4014"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.go-inicio.com\/en\/wp-json\/wp\/v2\/media\/3915"}],"wp:attachment":[{"href":"https:\/\/www.go-inicio.com\/en\/wp-json\/wp\/v2\/media?parent=3965"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.go-inicio.com\/en\/wp-json\/wp\/v2\/categories?post=3965"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.go-inicio.com\/en\/wp-json\/wp\/v2\/tags?post=3965"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}