35
The way you are doing it will work but it will take too much time, since postgis is trying to create the geometry of the intersection of every "blockgroup vs town" combination, even when they don't even touch.
Add another condition check to your WHERE clause to check if the two geometries intercept, and put it before the existing one:
select b.*,t....
29
You can do it in QGIS using symbol transparency, feature blending mode and symbol color.
Notice the difference between Layer Transparency and blending mode(that will be applied to all features) and the symbol transparency and feature blending mode that will stack with other features in the same layer.
All seetings are available in Layers Properties > Style....
26
To remove duplicates:
You can use the Delete duplicate geometries tool by accessing it via the Processing Toolbox:
Another option is to use the v.clean tool from GRASS and select the rmdupl option:
To remove overlaps:
You can use the Dissolve tool, provided there are common attributes between the original polygon and the overlapping polygon:
As always, ...
22
A self-join allows you to operate on the relationship between pairs of two features. But I don't think you're interested in pairs: for each feature, you want to operate on the relationship between that feature and all other features in your dataset. You can accomplish this with a subquery expression:
CREATE TABLE parcels_trimmed AS
SELECT id, ...
20
In the situation where you only need to know whether a table contains any overlapping polygons, and you're not concerned with the size or abundance of overlaps, I recommend a query of the following form:
SELECT *
FROM my_table a
INNER JOIN my_table b ON
(a.geom && b.geom AND ST_Relate(a.geom, b.geom, '2********'))
WHERE a.ctid != b.ctid
LIMIT 1
...
19
Enable Topology Checker Plugin in Plugin Manager. Add your polygonal layer in Topology Rule Settings window, select "must not overlap" rule and add them. To see overlap errors click on Validate button.
17
Adding to Alexandre's very useful answer, if some of your census units may span three of your towns (and therefore you can't guarantee more than 50% falls in any town) you can do this:
select distinct on (b.id)
b.*,t.name,
(st_area(st_intersection(b.wkb_geometry, t.wkb_geometry))/st_area(b.wkb_geometry)) as proportion
from blockgroups b, towns t
where ...
17
The GeoDataFrame
import geopandas as gpd
g1 = gpd.GeoDataFrame.from_file("poly_intersect.shp")
g1.shape
(4, 3)
1) You can use the itertools module
a) If you want to merge the intersections of the overlapping polygons
import itertools
geoms = g1['geometry'].tolist()
intersection_iter = gpd.GeoDataFrame(gpd.GeoSeries([poly[0].intersection(poly[1]) for poly ...
17
Try renderers: "Point displacement" or "Point Cluster" from symbology menu. (QGIS 3.x required for point cluster.)
As point displacement they can look like this for example:
And as point cluster like this for example:
Play around with the settings to fit your needs. You can combine these with graduated, categorized or rule based ...
16
I recommend that you try using the Intersect (Analysis) tool with one input.
According to How Intersect works (with my bolding):
Intersect can run with a single input. In this case, instead of
discovering intersections between the features from the different
feature classes or layers, it will discover the intersections between
features within the ...
14
This is a really nice application for a PostgreSQL trigger. To set up a trigger in PostgreSQL, you do two things:
Create a user-defined function that is run whenever a trigger is called (can be a row insert, update, or delete)
Use a CREATE TRIGGER statement to bind that function to a particular table for a particular operation (in this case, INSERT).
Here'...
13
There is a possibility using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...
Let's assume we have three features in 'vegetation' (green) and four in 'treatment' (red) accordingly, see image below.
With the following Query, it is possible to add a field to the attribute layer of the vegetation layer to indicate if any ...
12
areas = []
for line_feature in line_layer.getFeatures():
cands = area_layer.getFeatures(QgsFeatureRequest().setFilterRect(line_feature.geometry().boundingBox()))
for area_feature in cands:
if line_feature.geometry().intersects(area_feature.geometry()):
areas.append(area_feature.id())
area_layer.select(areas)
12
Add a new field to both layers to hold the original areas and call it Area
Calculate the areas for all the polygons in each layer and put into the Area field
Execute a union of layer 1 and layer 2 - make Layer 1 target layer and include FIDs
Add two new fields to the union result - AreaCalc and PC_Overlap
Calculate the areas for the resulting union layer ...
12
Try this expression:
array_to_string(overlay_intersects('Layer_A', name))
(assuming the City names are stored in name field in the 'Layer A').
11
One way of doing this is cloning the layer, using definition queries and labelling them separately, using upper-left only label position for the first layer and lower-left for second.
Add THEFIELD type integer to layer and populate it using expression below:
aList=[]
def FirstOrOthers(shp):
global aList
key='%s%s' %(round(shp.firstPoint.X,3),round(shp....
11
You can use the 'Check Geometries' plugin (Vector/Geometry Tools/Check Geometries) to remove overlapping areas.
11
It is very complicated task known as bin packing problem.
The script below produces one of countless sub-optimal solutions. Algorithm:
places fish net over rotated POLYGON to find out rotation angle in range (0,175,5) that result in maximum count of complete rectangles
breaks if no such rectangles found, otherwise
un-rotate every good rectangle and append ...
11
In QGIS I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...
Let's assume we have two polygon layers 'Layer_A' (brown) and 'Layer_B' (green) respectively, see image below.
With the following query, it is possible to achieve the result, i.e. to perform a spatial join and put the result comma separated in ...
11
Fortunately, with the prio dependency, this is easier to solve than a purely recursive difference aggregation.
I'd prefer the LATERAL statement (here in full verbose mode), both for clarity of the statement and performance
SELECT ST_Multi(COALESCE(
ST_Difference(a.geom, blade.geom),
a.geom
)) AS geom
FROM table1 AS a
CROSS JOIN ...
11
First make sure your Buildings have a unique "id" (which does not contain a | character, you will see why below). You can add it for example via field calculator by adding a new field with the expression $id if they do not have one already.
Then run the "Polygon Self-Intersection" from SAGA Processing tools and choose this "id" ...
10
This can be easily accomplished using PostGIS. Preview the results using a modified version of the query below:
SELECT a.id, b.id, ST_Area(a.shape), ST_Area(b.shape)
, 100*(ST_Area(ST_Intersection(a.shape, b.shape))/LEAST(ST_Area(a.shape), ST_Area(b.shape)))::numeric(5,2)
as pct_overlap
, ST_Union(a.shape, b.shape) as new_shape
FROM ...
10
Take your model layer and in the attribute table, add a new field called area_mod for example.
Note that there are two ways to generate area:
$area is an ellipsoid based calculation using not just the area units but also factors in the projects spatial reference system (SRS) ellipsoid
area($geometry) is planimetric where is uses only the area units of the ...
10
True Polygon containment is a costly operation; not only does a containment check have to run intersection computations between each pair of vertices in one polygon for each pair of vertices in the other, but is it also impossible to improve by subdividing any of them.
That being said, your query is also not going to return the desired result; you are ...
9
I would recommend using the Count Overlapping Features (Analysis) tool.
Generates planarized overlapping features from the input features. The
count of overlapping features is written to the output features.
9
You can do this with 2 tools, Feature to Polygon and Spatial Join
First, run your polygons through Feature to Polygon. Delete any of the attributes you don't need from the output (I still got them even when I turned preserve attributes off):
Then, run Spatial Join:
The target features are the output of Feature to Polygon
The join features are your ...
9
QGIS Processing Toolbox has a SAGA Polygon self-intersection tool (under SAGA | Vector polygon tools).
It works much like QIGS Union Tool, while this tool retains only one feature per the overlapped area.
Open the attribute table of generated Intersection layer and select Delete all features whose fid (or id) is 0.
9
Since QGIS 3.8, it's now included in QGIS core, without plugin or coding.
In the Processing Toolbox, search for Overlap and open the Overlap analysis:
Source: http://changelog.qgis.org/en/qgis/version/3.8/#overlap-analysis
9
If your Ecuador layer is several polygons, I would start off by running the "Dissolve" tool. This will create 1 merged feature.
Following that, I would run the "Multiparts to Singleparts" tool on the dissolved layer. This is because you have some disconnected polygons. You should now get like 5-10 features (based on your screenshot), but ...
9
You can dissolve without a dissolve field but Im not sure if that is what you want.
I suspect it is:
Dissolve
Multipart to singleparts
Field calculate @row_number
Intersect output with your original data. Now you have your input data with a group attribute:
Only top voted, non community-wiki answers of a minimum length are eligible