6

Is the project file in QGS or QGZ format? If it is QGS, open the file in a text editor and do a find and replace on the old and new hostnames or ip addresses. If the project is in QGZ format, open the QGZ file with 7-Zip or the file compression program of your choice. Extract the QGS file in the QGZ file, update the hostname or address and put the updated ...


5

my answer is based on: http://www.postgresqltutorial.com/creating-first-trigger-postgresql/ and https://stackoverflow.com/questions/39607334/postgres-trigger-creation-error-no-language-specified-sql-state-42p13 EDIT1: adjusting for ThingumaBob's comment first you need to create a function to be used by your trigger action : CREATE OR REPLACE FUNCTION ...


3

Just like with the COUNT(*), the SELECT can utilize (almost) every other aggregate function, keeping the reference to the grouped rows defined by the GROUP BY. Not directly obvious: in your first query you implicitly GROUP BY ST_SnapToGrid(...) already, by using the column expression alias grid. With that in mind, just run this very similar query: SELECT ...


3

would something like this do the job? UPDATE1: used st_crosses instead of st_intersects and not st_equals and created an index for the geometry UPDATE2: instead of calculating a huge multipoint, lets calculate one multipoint per join_id and let's split only the line with this join_id. Also, if a particular join_id has only one segment bring it anyways ...


2

The short answer is: Yes! ogr2ogr has no problem reading materialized views. As user30184 pointed, my issues were caused by quoting errors. The correct Python code should be like this: table_name = '"my_schema_name"."my_TABLE_name"' Notice, double quotes around both, schema and table name, and single quotes wrapping whole string.


2

It should be possible to make a conversion with one ogr2or command by reading data as /vsizip/vsicurl/[https://address_to_zip] as documented in https://gdal.org/user/virtual_file_systems.html. Test with the URL that is used in the document goes well ogrinfo -ro -al -so /vsizip/vsicurl/https://raw.githubusercontent.com/OSGeo/gdal/master/autotest/ogr/data/...


2

As @IanTurton pointed out, enclosing (multiple) column names in parenthesis will invoke a cast to RECORD. Simply run CREATE OR REPLACE VIEW esfuerzo AS SELECT DISTINCT eventdate, geom FROM datos WHERE orden = 'x' ; or possibly better, the equivalent CREATE OR REPLACE VIEW esfuerzo AS SELECT geom FROM datos WHERE orden =...


2

I finally found out what the right instruction is. Please, see below: load_to_pg = "INSERT INTO public.vml_polygs (objectid, fcode, shape_length, shape_area, geom) VALUES ({0}, {1}, {2}, {3}, ST_GeomFromText('{4}', 27700));".format(index, row[0], row[1], row[2], str(row[3]))


2

Another approach is to use one of the GDAL utilities - OGR2OGR. Using this you can upload your feature class to your database and create a new table. You can use this command line tool as follows ogr2ogr -f PostgreSQL PG:"host=xxxx dbname=xxxx user=xxxx password=xxxx" path_to_gdb test -nln vml_polygs This effectvely says, output to PostgreSQL format in the ...


2

You could try calculating a compactness score for your geometries to see if they are a circle. Something like the Polsby-Popper test will calculate a ratio between 1 and 0, 1 being a perfect circle and any other geometric shape will have a smaller ratio. 4 * pi() * (area/(perimeter^2)) If you are working with perfect circles you can select anything with a ...


2

First of all, a geohash is easier to explain with referencee to points, but the logic can easily be extended to a grid, using two points, for opposite corners, similar to how ST_MakeBox2D works. A geohash in made up of interwoven bits, where each even bit represents increasing precision (powers of two in longitude) and each odd bit represents increasing ...


2

Building on what @JohnPowell said in the comments, you can make a join that is based on a spatial intersection. If you have a spatial index setup, this should have much better performance than a cross join. If you do not have a spatial indexes, you can create them directly in PostgreSQL using SQL or use the QGIS DB Manager to create them with a GUI. Example ...


1

CREATE TABLE schema.polytable AS SELECT column1, column2, ST_AsText(ST_CONVEXHULL(ST_COLLECT(geomcolumn))) AS newgeomcolumn FROM schema.sourcetable GROUP BY column1, column2 As suggested by someone else, this works in this scenario.


1

In Postgres, you can join and query the same layer: once for the "source", and one for the connected neighbors. Grouping by source ID will let you count the connected neighbors and will also open the door to aggregate functions. A spatial index is required if you want a descent execution time. SELECT a.id, count(*), string_agg(b.id::text,',') as neighbors ...


1

The error message mentions " 0101000000A7E8482EFFC", with a trailing space, which makes the geometry invalid. The dirty solution is to trim your data SELECT ST_AsText(TRIM(GPS)) from schema.table; with src as (select ' 0101000000A7E8482EFFC55B402063EE5A42064140'::text txt) select st_asText(txt::geometry) from src; ERROR: parse error - invalid geometry ...


1

You can force the geometry type and projection in the view definition by casting the geometry: create view tv as select geom::geometry(POINT, 4326) from myTable; jgtest=> \d tv View "public.tv" Column | Type | Collation | Nullable | Default ------------+----------------------+-----------+----------+------...


1

Setting a different target schema is currently not supported by osm2pgsql. See this GitHub issue for details: https://github.com/openstreetmap/osm2pgsql/issues/768


1

If I understand translat your question correctly, I would go in this direction: 1) collect all the lines, for example by street name or by condition if they (segments) touch each other; 2) create two copies of them: SELECT * INTO roads_1 FROM source_table; SELECT * INTO roads_2 FROM source_table; 3) remove intersecting streets: DELETE FROM roads_1 as ...


1

You can't use a pseudo-type as a column type. Since geom and eventdate are different types I suspect you get a sort of undefined type in the results. You may be able to cast it to something specific or could maybe try: create view esfuerzo as select distinct (st_astext(geom),eventdate), geom from datos where orden='x'; to make the result a varchar.


1

PostgreSQL/PostGIS implement the SQL/MM specification, so just write it in proper SQL: SELECT * FROM my_border_table t1 JOIN my_polygon_table t2 ON ST_Intersects(t1.geoloc, t2.geom) WHERE ST_Dimension(t1.geoloc) = 2 AND ST_Dimension(t2.geom ) = 2; -- or: -- ST_GeometryType(t1.geoloc) IN ('ST_Polygon', 'ST_MultiPolygon') -- AND ST_GeometryType(t2....


1

You can use the 'export to Postgres' tool in QGIS. You'll notice there is a tick box at the bottom for 'overwrite' - if you make sure this is ticked your old layer will be updated with the new features.


1

Search for line segments with same source and target id in your data. You possibly would get them


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