[postgis-users] Excel connecting and inputting into PostGIS

[Shaozhong SHI]

Scenario:  Many Excel users seek to contribute data to PostGIS.  Excel can be connected to PostGIS via ODBC.

Is there a simply way for users to load their data into PostGIS?

Regards,

Shao

[Imre Samu]

> Is there a simple way for users to load their data into PostGIS?

CSV?

-  save as a CSV file  ( contains LAT , LON columns )

 - import CSV to Postgres
-  convert LAT/LON  to geometry columns ... 
https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table

or scripting?  ( Python; Julia, R , ...  )

or any DB tool?  ( https://dbeaver.com/docs/wiki/Data-transfer/ ; PGAdmin4: https://www.youtube.com/watch?v=Ikd2xSb00UI )

Imre

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Paolo Cavallini]

The easiest I know of is through QGIS.
Cheers.

--
Please excuse my brevity.

[Shaozhong SHI]

We have been doing that way for a long while.

I just wonder whether there can be a way to set things up, and Excel users can easily upload data, while you are on leave and spending a holiday on golden beaches. 

[ruv...@beamerbrooks.com]

SQL Workbench/J can read/write Excel files directly.   It's pretty much a replacement for pgAdmin and for most of the things that are done in psql.

Ruven Brooks

[Shaozhong SHI]

On Sat, 15 Aug 2020 at 17:41, <ruv...@beamerbrooks.com> wrote:

SQL Workbench/J can read/write Excel files directly.   It's pretty much a replacement for pgAdmin and for most of the things that are done in psql.

Ruven Brooks

That sounds interesting.  Can we adapt it in such a way, users just click in Excel and data gets uploaded.

Before you are to go on holiday, simply drop them a note what to do.  Bear in mind that they have no clue of SQL. 

[Bruce Rindahl]

By far the easiest way is through a foreign data wrapper.  Here are step by step directions:

https://github.com/pramsey/pgsql-ogr-fdw

I have used this to directly connect to an Excel table, no importing required.  If you add data to the excel  table and don't change the structure,  postgresql picks up the changes.

If you need to use gis data, put lat/long data in separate columns.  Then use a view to create a point from the two columns.

To give you an example, I download earthquake data from the USGS every 5 minutes in CSV format and link to a PostgreSQL database via the above method.  Queries then compute the distance to selected locations and notify me if any one of the locations might be effected.  All automatic.

The method above handles every format that ogr can handle.

[Jeff Norville]

I wrote a little VBA module to connect Excel to various databases, including Access and SQL Server, then use ADO to read/write data to existing tables. Originally this was to work with chemical analytical data with certain geographic identifiers.

This is problematic for many postgis databases because they can be harder to query to reduce rows enough for Excel to manage.

I'll take a look at adapting this to postgresql if there is interest...? We all have some time on our hands now, right?

--Jeff