Hot answers tagged

8

See: https://stackoverflow.com/questions/7595635/how-to-convert-polygon-data-into-line-segments-using-postgis : SELECT st_length(ST_MakeLine(sp,ep)) FROM -- extract the endpoints for every 2-point line segment for each linestring (SELECT ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp, ST_PointN(geom, generate_series(2, ...


5

Rather than a series of set-wise JOINs (that necessarily creates extremely inefficient cartesian products), use a correlated expression during a traversal of your polygons table - that way you can fully benefit from indexes on both rides.start_geom & rides.end_geom. If possible, I favor a LATERAL expression: SELECT ply.uid_key AS "PolygonUID", ...


5

Here's some simple SQL for a spiral: WITH spiralStep AS ( SELECT i, 80 AS circleSegs, -- Parameter: quantization of arc 1000 AS centerX, -- Parameter: center X 1000 as centerY, -- Parameter: center Y 100 as radius -- Parameter: radius FROM generate_series(0, 5 * 80) t(i) -- Parameter: # rings = 5 ) ...


3

From the screenshot you can see that the SQL builder is used to filter a given layer, so the expression you can write is only a where clause, which is automatically appended to select * from givenLayer where . You can either load the layer from the DBManager (maybe include an ID field?) or do roughly the same using a virtual layer. Go the the menu layer / ...


3

The Query Builder is not the same as the DB Manager. DB Manager allows you to query using SQL syntax for various databases, but the Query Builder uses the QGIS expression engine. See https://docs.qgis.org/2.18/en/docs/user_manual/working_with_vector/expression.html I would just use the DB manager and create a layer from the query. But ff you want to do this ...


3

There's a reason this very popular problem and task has no generic answer: it needs to be tailored to your data to produce predictable results - and has to be implemented with different algorithmic approaches. In terms of algorithmic theory you are required to set up specific rules to address your specific case. On top of that you will often need to create ...


3

Building off what @robin loche and @Paul Ramsey mentioned I threw together a crude example of a function that assesses the map zoom level and weeds out features that based on square footage. For the function below if the map zoom Z level is 14 or greater (zoomed in) then any geometry with a square footage of 100 or greater will be displayed. if its zoom ...


2

You're mixing and matching in a weird way. You either want to store your spatial data in PostGIS or as a GeoDataFrame. If you want to store the data as a PostGIS table, you can use GeoPandas to import the data into PostGIS, and at that point you have the entire PostgreSQL/PostGIS querying engine at your disposal: poly = gpd.read_file('polygon.shp') engine = ...


2

The number one thing you can do is ensure your version of PostGIS is as recent as possible, since pg_tileserv doesn't actually do any rendering at all, it just converts HTTP requests into SQL. It may just be that slamming N-thousand things into a tile takes a while. The number two thing you can do is place a caching proxy in front of your tile server, so ...


2

I have created a fix for my issue. I replaced the TileBBox function with the one below, allowing a 5th parameter to be passed with the tilesize. create or replace function TileBBox (z int, x int, y int, srid int = 3857, tileSize int = 256) returns geometry language plpgsql immutable as $func$ declare defaultTilesize numeric := 256; max ...


2

An inline SQL test to demonstrate what goes wrong and how to fix the problem. select ST_Within( ST_GeomFromText('POINT (326253.33 674110.63)'), ST_Buffer(ST_GeomFromText('POINT (326253.33 674110.63)',27700),10)); ERROR: contains: Operation on mixed SRID geometries (Polygon, 27700) != (Point, 0) SQL state: XX000 select ST_Within( ST_GeomFromText('POINT (...


2

I have used one of the lizmap example project. You can set url to get only filtered data shown. To use filter you have some condition. The layer attribute table as to be accessible it activate a data menu with a filter option. The structure is : https://demo.lizmap.com/lizmap/index.php/view/map/?repository=feat1&project=observatoire&filter=LAYERNAME:&...


1

Apparently, the table in the geopackage was faulty, I guess. I had to re-save the shapefile to the geopackage to overwrite it. And when I ran the query in the Query Builder/Filter window, it worked.


1

From the docs: ST_TOUCHES Returns TRUE if the only points in common between g1 and g2 lie in the union of the boundaries of g1 and g2. In other words, you're seeing if your buffered points share an edge with the linestring. You want ST_DWITHIN. I don't have your data but try: SELECT COUNT(*)/ST_LENGTH(routea.geom)::float as Count FROM flickr_edin, routea ...


1

You need to specify the SRID of the table you create. To create it with the same SRID as the flickr_edin table: CREATE TABLE temppat (id serial, geom geometry(Geometry,27700)); INSERT INTO temppat (geom) VALUES (ST_GeomFromText('POINT (326253.33 674110.63)')); To find out the SRID(s) of a table: SELECT DISTINCT ON (ST_SRID(geom)) ST_SRID(geom) FROM ...


1

I see you marked your post with the "postgis" and "osm2pgsql" tags, so I am assuming you are using these tools. If so, and you run a system with the latest v1.5.1 of osm2pgsql supporting the new "flex output" option of osm2pgsql, then there is the work of Paul Norman on the draft version of the flex Lua style file of '...


1

I fully agree with the caching suggested, it's almost always necessary for a production environment. As another improvment idea, maybe you can try to make a function to generate your tiles. Pg_tileserv is good and easy for serving small table, but for big tables I think it's often better to make it serve a function instead of the table directly. For example ...


1

You must provide all required parameters. Parameter values must have proper type, e.g. setting tile size tw=100 is correct, but tw=100.0 will not work. CREATE TABLE rasters.global_dem_retiled AS SELECT st_retile( 'rasters.global_dem', -- source raster table 'rast', -- raster column ST_SetSRID(ST_Extent(...


Only top voted, non community-wiki answers of a minimum length are eligible