[postgis-users] ArcGIS desktop accessing and reading from PostGIS

Shaozhong SHI shishaozhong at gmail.com
Mon Aug 26 09:40:26 PDT 2019 

Hi, Andy,

How can a schema set and used properly, so that ArcGIS, MapInfo and QGIS
can read and understand geographical data properly?

Regards,

Shao

On Mon, 26 Aug 2019 at 16:47, Andy Anderson <aanderson at amherst.edu> wrote:

> Postgres Book says:
>
> 1) A database *cluster* is a collection of databases.
>
> 2) A *database *can have many schemas.
>
> 3) A *schema *is a collection of tables, indices, functions, data types,
> and operators. Essentially it is a name space that can be used to
> compartmentalize particular purposes that might have duplicate names, a
> development version and a production version (e.g. develop.mapit and
> product.mapit). If you only have one schema you don’t need to refer to it;
> if you have more than one there is a lookup order if the schema is not
> referenced.
>
> — Andy
>
> On Aug 23, 2019, at 12:05 PM, Shaozhong SHI <shishaozhong at gmail.com>
> wrote:
>
> Hi, Bobb,
> Fundamentally, PostGIS/PostgresSQL can be used to create a database.  When
> one creates a table, the table is defined.  Namely, a schema is created and
> defined.
> Am I right?
>
> Can we define a schema so that all other applications including ArcGIS,
> MapInfo and QGIS can read?
>
> Regards,
>
> Shao
>
> On Fri, 23 Aug 2019 at 14:29, Basques, Bob (CI-StPaul) <
> bob.basques at ci.stpaul.mn.us> wrote:
>
>> Correct.  One or more tables can be defined in a schema.
>>
>>
>> On Aug 23, 2019, at 3:36 AM, Shaozhong SHI <shishaozhong at gmail.com>
>> wrote:
>>
>> *Think Before You Click: *This email originated *outside *our
>> organization.
>>
>> Hi, Bobb,
>>
>> A database has many schemas.
>>
>> A schema is a definition of a data structure.  Many tables can be created
>> by using this data structure.
>>
>> Am I right?
>>
>> Regards,
>>
>> Shao
>>
>> On Thu, 22 Aug 2019 at 17:25, Basques, Bob (CI-StPaul) <
>> bob.basques at ci.stpaul.mn.us> wrote:
>>
>>> Shao,
>>>
>>> I’ve always thought of a schema as a sub-grouping inside of a database,
>>> as in a database can contain many schemas.
>>>
>>> Or, from a higher point of view . . .
>>>
>>> Database instances (Postgres, Oracle, MySQL, etc) contain one or more
>>> databases, and each database can contain one or more schemas . . .
>>>
>>> bobb
>>>
>>>
>>>
>>> On Aug 22, 2019, at 11:07 AM, Shaozhong SHI <shishaozhong at gmail.com>
>>> wrote:
>>>
>>> *Think Before You Click: *This email originated *outside *our
>>> organization.
>>>
>>> Hi, Bobb,
>>>
>>> Thanks.
>>>
>>> A lot of technical terms used are interchangeable.
>>>
>>> Am I right to say this? When a postgreSQL database is created, a data
>>> structure is created.  This data structure is referred to as a scheme?
>>>
>>> Namely, in this instance, a scheme = a data structure created with
>>> postgreSQL executables.
>>>
>>> Am I right?
>>>
>>> Regards,
>>>
>>> Shao
>>>
>>> On Thu, 22 Aug 2019 at 16:37, Andy Anderson <aanderson at amherst.edu>
>>> wrote:
>>>
>>>> Thanks for the correction, Paul, I guess I’ve been relying on my MySQL
>>>> experience, which stores user data in the "mysql" database.
>>>>
>>>> — Andy
>>>>
>>>> On Aug 21, 2019, at 12:59 PM, Paul Ramsey <pramsey at cleverelephant.ca>
>>>> wrote:
>>>>
>>>> This is not actually so. There’s nothing special about the postgres
>>>> database, drop it and see. The system information lives in system catalogs
>>>> that are not attached to any particular database. The template0 and
>>>> template1 databases are “special” in that new databases are created by
>>>> copying the contents of those templates, so alterations to them will
>>>> percolate into any new databases created, so they should generally be left
>>>> alone (unless you have a reason to want something included in all new
>>>> databases). If you drop template1 you will find it hard to create new
>>>> databases (unless you specify a different template) but otherwise things
>>>> will keep running.
>>>>
>>>> Anyways, I imagine the mystery of the Esri connection to ‘postgres’ is
>>>> just that someone at Esri thought the same as you, seeing that most builds
>>>> of postgres do ship with a postgres database already created, so that the
>>>> postgres database user can easily have something to connect to that is not
>>>> one of the templates. But it’s not special, it’s just a database.
>>>>
>>>> P.
>>>>
>>>> On Aug 21, 2019, at 9:33 AM, Andy Anderson <aanderson at amherst.edu>
>>>> wrote:
>>>>
>>>> The postgres database is set up by default by the postgres server and
>>>> holds configuration information: databases, users, etc. Don’t touch. Set up
>>>> another database for Arc to use.
>>>>
>>>> — Andy
>>>>
>>>> On Aug 21, 2019, at 12:23 PM, Basques, Bob (CI-StPaul) <
>>>> bob.basques at ci.stpaul.mn.us> wrote:
>>>>
>>>> That’s what I’m talking about.  when I first tried to connect to
>>>> Posters (and the “postgres” named database, I kept getting an error from
>>>> ArcGIS saying the the “postgres” name for a database was a bad thing, and
>>>> it wouldn’t connect.  After much searching, I discover that the name,
>>>> “postgres” for a database wasn’t allowed by the ArcGIS client to connect
>>>> to.
>>>>
>>>> As long as you don’t try connecting to the default “postgres” database,
>>>> all seem to work fine.
>>>>
>>>> bobb
>>>>
>>>>
>>>>
>>>> On Aug 21, 2019, at 10:59 AM, Shaozhong SHI <shishaozhong at gmail.com>
>>>> wrote:
>>>>
>>>> *Think Before You Click: *This email originated *outside *our
>>>> organization.
>>>>
>>>> Hi, Bobb,
>>>>
>>>> I am confused with this.
>>>>
>>>> The idea is to use ArcGIS desktops to read data from PostGIS/PostgreSQL.
>>>>
>>>> How best to configure and make them happy to talk to each other?
>>>>
>>>> Regards,
>>>>
>>>> Shao
>>>>
>>>> On Wed, 21 Aug 2019 at 15:01, Basques, Bob (CI-StPaul) <
>>>> bob.basques at ci.stpaul.mn.us> wrote:
>>>>
>>>>> Shao,
>>>>>
>>>>> I had to dig fairly deeply into the ESRI site to find this particular
>>>>> explanation for why the service couldn’t/wouldn’t get connected.  Just to
>>>>> be clear, we do use logins for accessing the database, we just have chosen
>>>>> to use the “postgres” (named) database.
>>>>>
>>>>> So, I just went and tried to find the refence I found previously, and
>>>>> can’t find it (real quick).  Anyway,as long as you don’t try to connect to
>>>>> the “postgres” database, things do seem to work ok.
>>>>>
>>>>> bobb
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Aug 21, 2019, at 2:18 AM, Shaozhong SHI <shishaozhong at gmail.com>
>>>>> wrote:
>>>>>
>>>>> *Think Before You Click: *This email originated *outside *our
>>>>> organization.
>>>>>
>>>>> Hi, Bob,
>>>>> Can you point me to excellent technical documentation on what you
>>>>> mentioned, so that I can double-check.
>>>>> My colleagues have made it working, but there are some issues.  For
>>>>> instance, not all ArcGIS desktops can access and retrieve data.
>>>>>
>>>>> Looking forward to have more insight into this matter.
>>>>>
>>>>> Regards,
>>>>>
>>>>> Shao
>>>>>
>>>>> On Tue, 20 Aug 2019 at 17:08, Basques, Bob (CI-StPaul) <
>>>>> bob.basques at ci.stpaul.mn.us> wrote:
>>>>>
>>>>>> All,
>>>>>>
>>>>>> One biggie, is that the ESRI side does not like connecting to the
>>>>>> “postgres” named database, claiming it’s a System database (whatever that
>>>>>> means).  This is not readily available as a limitation either when looking
>>>>>> for reasons why it won’t connect.  We’ve been using the “postgres” database
>>>>>> name for years (a decade now??) with no ill effects from other
>>>>>> applications/ connections.  I sort of understand this from the commercial
>>>>>> side having worked with other databases like Oracle and MSSQL, but still,
>>>>>> there should be a way to side step this with a setting somewhere.
>>>>>>
>>>>>> I looked high and low for a way to alias the “postgres” database name
>>>>>> inside of Postgres, but didn’t find anything very easy to implement or
>>>>>> maintain very easily.  I did think about setting up another dataabase and
>>>>>> using Views to the postgres database, but that seemed like a bad idea for
>>>>>> some reason, along with being somewhat hard to maintain, but upon
>>>>>> reflection I keep coming back to the idea.
>>>>>>
>>>>>> All non “postgres” named connections seem to work fine so far.
>>>>>> Haven’t implemented anything yet that is really heavy duty as far as
>>>>>> loading though, so who knows.
>>>>>>
>>>>>> Anyway, just my 2cents.
>>>>>>
>>>>>> bobb
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Aug 20, 2019, at 10:12 AM, Shaozhong SHI <shishaozhong at gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>> *Think Before You Click: *This email originated *outside *our
>>>>>> organization.
>>>>>>
>>>>>> How to ensure that all versions of ArcGIS desktop applications can
>>>>>> access and read from server-based PostGIS/PostgreSQL?
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>> Shao
>>>>>> _______________________________________________
>>>>>> postgis-users mailing list
>>>>>> postgis-users at lists.osgeo.org
>>>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> postgis-users mailing list
>>>>>> postgis-users at lists.osgeo.org
>>>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at lists.osgeo.org
>>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at lists.osgeo.org
>>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org
>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org
>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org
>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org
>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org
>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190826/40529b11/attachment.html>

More information about the postgis-users mailing list