pgAdmin 4 has a few useful things I should remember:
Query History is really neat. Second tab in the Query Editor page, shows a history of all the queries made and the outcome messages. Easy to find what you have already done.
Ctrl Space triggers code completion. Not quite as good as an IDE but helpful none the less
Keep you columns lower case and you wont have to use quotes for your tables in table queries.
I discovered this by reading the Postgres Conference Slides. I am writing this down so I dont forget, as is the case for almost everything I put here.
I was reading the presentation by Regina Obe, co-author of several wonderful books on PostGIS, Postgres, and more.
You can find links to their presentation at Planet Postgis entry for March 25, 2019, or at Postresql Online Journal entry PGCONF 2019 data slides up.
Direct link to the PDF slides look under the section Other Relational Databases, but the rest is interesting as well.
My use case here is Windows PostGIS to Windows SQL Server over an enterprise domain using is an ODBC connection.
First I need to set up a Windows DSN entry for the ODBC connection. Open the ODBC Data Source, 64 Bit in my case, and add the system data source. When the connection attempt is made, it will use this to find the remote server on the domain.
My connection looks like this, with pguser and pguser as database name.
CREATE SERVER svr_sqlserver FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (datasource 'ODBC:pguser/pguser@dnsentryname',format'ODBC');
CREATE SCHEMA IF NOT EXISTS ss;
IMPORT FOREIGN SCHEMA "dbo" FROM SERVER svr_sqlserver INTO ss
I do notice I am not getting any Spatial Data in my Foreign Tables. This is somewhat expected as SQL server does not natively send spatial data accross linked servers. However, the presentation says it should so I might need to revisit this.
I loaded a Geodatabase with FME into Postgis recently. The data were polygons.
After loading I noticed it was taking a long time for the connection to be made, there were a lot of errors which isn’t that unusual due to some of the data originally coming from CAD and doesn’t meet OGC requirements.
That was not all though, there was something else going on with these data. I ran check geometry and ST_IsValid which told me that there were a whole lot of Ring Self-Intersection errors as expected.
Using this query:
SELECT ST_GeometryType(geom) as geomtype
FROM public."MyTable"
group by geomtype
I found that the database had stored 4 kinds of geometry:
“ST_CurvePolygon”
“ST_MultiPolygon”
“ST_MultiSurface”
“ST_Polygon”
This was surprising because the source was a File Geodatabase and should only have one type of geometry, which should store as ST_MultiPolygon.
I am not sure if FME recreated these geometries or if the File Geodatabase itself was a geometry collection.
Additionally, using the Database Manager tool in Qgis, the table would not load as there were too many errors, that I think were QGIS enforcing the single geometry datatype.