A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.
6

You need to alias your columns, effectively giving it a name, if they are the result of a function, or PostgreSQL will use the function name of the outermost function it uses to generate the column (values)! CREATE OR REPLACE VIEW _06_spildevand_vandloeb.drikkevandsledning_dissolve AS SELECT ROW_NUMBER() OVER() AS id, ST_Union(the_geom)::...


3

If you use the PostGIS function st_equals instead of the = operator, the query planner will be able to use the geometry index and work more efficiently. You can check if that's actually happening by checking the query plan using the EXPLAIN command before your query.


2

PostGreSQL (and thus PostGIS) don't like table (or column) names to start with digits or to have mixed case. So you need to add " around any name that does this. So, in your case the select statement is: SELECT geom FROM "12NM_PolyGrid" which in a python string should probably be: 'SELECT geom FROM "12NM_PolyGrid"'


2

While you cannot create a primary key on a view, you can create a column having unique distinct values which you can use as a primary key in QGIS. CREATE OR REPLACE VIEW public.myview AS SELECT ROW_NUMBER() OVER(ORDER BY t1.id, t1.name, t1.blablabla) AS V_UID, t1.* FROM t1 WHERE t1.column = 'some_condition'; What is extremely important is ...


2

For proximity searches better use ST_DWithin, as it is highly optimized to use the spatial index: SELECT DISTINCT a.accidents, a.geom FROM roads AS r JOIN accidents AS a ON ST_DWithin(r.geom, a.geom, <distance>) WHERE r.roadname = 'A3' ; This will find all accidents that are within <distance>¹ of any segment with name A3; ...


2

Understanding grouping in SQL is essential in everyday DB usage, and it has the potential to get diabolically complicated to manage across complex queries if unfamiliar. At the core, a GROUP BY statement will uniquely categorize rows across the given distinct column(s) values; same values go into the same category, with their respective rows being collected....


2

First of all, the trigger should be on buffer table. Secondly, you should insert the intersection (now you claculate it but then leave it behind). I used the following codes to create 3 tables: create table buffer (id serial primary key ); select addgeometrycolumn('buffer', 'geom' , 3979 , 'POLYGON' , 2); create table polygons (id serial primary key ); ...


2

While PostGIS uses Well-Known Binary internally, you'd be better off explicitly casting the PostGIS geometry to Well-Known Text or Binary. Try SELECT a.objectid, a.shape, b.objectid AS oid FROM cd.points a JOIN sw.risk_polygons b ON sde.st_intersects(a.shape, sde.st_geometry(ST_AsText(b.shape), 4326)) = 1 ORDER BY a.objectid; or SELECT ...


2

You can add new fields in the view definition: CREATE OR REPLACE VIEW v_roads as SELECT r1.id, r1.geometry, 1 as road_type, 'type1' as road_type2 FROM roads_1 r1 UNION ALL SELECT r2.id, r2.geometry, 2 as road_type, 'type2' as road_type2 FROM roads_2 r2 UNION ALL SELECT r3.id, r3.geometry, 3 as road_type, 'type3' as ...


2

Try to reinstall PostGIS by the following executable on this page : http://download.osgeo.org/postgis/windows/pg12/ The download will allow you to get the necessary dll files. When PgAdmin4 was released, when you installed PostGIS with StackBuilder, some files were missing. They updated the executable a few days later and the problem was solved.


1

If you're able to alter the source data, that would be a good place to start. UPDATE konflikt.source_table SET roadstatus = 'Not mentioned' WHERE roadstatus = '' If that isn't possible, adding a CASE statement to the roadstatus column in the materialized view is another option: create materialized view konflikt.vejstatus_dissolve as select ...


1

Here's a quick Java translation of the OS visual basic code from the spread sheet mentioned in the comments. import org.locationtech.jts.geom.Coordinate; import org.locationtech.jts.geom.GeometryFactory; import org.locationtech.jts.geom.Point; public class OSGridref { static GeometryFactory GF = new GeometryFactory(); static String[][] gridLetters = { {...


1

Go to <QGIS> | Settings | Options... | Data Sources | Data source handling and see if Evaluate default values is active; if so, deactivate it. QGIS can either request the default value (as per column definition) while editing (option active), or read the default value definition and pass it (or simply NULL) at save time (option inactive)! A nextval ...


1

GeoPandas will support postGIS from the next release. At this moment, you can try using this branch https://github.com/geopandas/geopandas/pull/1248 which brings that support or just get the code from it. Since geopandas 0.7.0 you will be able to do df.to_postgis(tablename, con=engine, schema=schemaname, if_exists='replace', index=False).


1

The error was that the column order in the SELECT query was mixed, it has to be SELECT id, geom, name, not SELECT id, name, geom. Thanks to @ThingumaBob for the solution.


1

wfgeo's comment reminded me of a join I made between the shapefile and a text file/table I had added in QGIS. All of the column names in the joined shapefile that wouldn't import are: OBJECTID,GEOID,SUFFIX,NAME,ALAND,AWATER,INTPTLAT,INTPTLON,OR_pop_fie,OR_pop_sta,OR_pop_cou,OR_pop_tra,OR_pop_blo,OR_pop_P01,area_ha,popdens_ha,area_sqmi,popd_sqmi I undid the ...


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