Group

Group Admins

  • Profile picture of Ethan Watrall

Institute for Digital Archaeology Method & Practice

Public Group active 2 years, 10 months ago

Discussion group for the members and faculty of the NEH Funded Institute for Digital Archaeology Method & Practice (http://digitalarchaeology.msu.edu) organized by Michigan State University’s Department of Anthropology and MATRIX: The Center for Digital Humanities and Social Sciences

Relationships in Database question

This topic contains 3 replies, has 3 voices, and was last updated by  Eric Kansa 3 years ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #718

    Ben Carter
    Participant

    All,

    I am trying to create “relationships” in a PostGIS database. Part of the problem may be that I am used to working in both MS Access and Filemaker Pro. I am trying to switch to using PostGIS, however. I can use pgadmin, but have been trying to stick to LibreOffice Base (as the open source alternative) and/or MS Access (because I know how to use that and I get it free from work).

    In the end, I am trying to link multiple tables of data (including Geospatial data). This is fairly easy in MS Access using “Relationships”. This allows columns (fields) with similar data to be linked. Then, a query can produce a table with data from both linked tables in one single all-inclusive table. The goal is to do this with PostGIS as well, but I am having a very hard time figuring it out. I can only find “JOIN” and “UNION” commands. I do see where a query can be used to create something similar, but the query would need to be redone each time I wanted a different arrangement of data. I want the “relationships” to exist outside of queries so that I don’t need to redo them for each new query.

    I can do this in QGIS as well in a limited manner, but the relationships exist in the QGIS file, not in the PostGIS file, even when the data is in the PostGIS file.

    I hope this makes sense. I have been struggling over this one for a while (and have resorted to using MS Access for some work that I would prefer to do in PostGIS).

    Please help!

    Thanks,

    Ben

    #719

    Jolene Smith
    Participant

    Hi Ben,

    I have no experience with PostGIS, but in my limited (but rapidly expanding!) experience beyond good old Access, it’s usually a matter of semantics between systems.

    Maybe you can create the relationships between tables right in SQL? https://digitalfellows.commons.gc.cuny.edu/2016/04/08/fun-times-with-sqlite-or-a-beginners-tutorial-to-data-management-and-databases-with-sql/ was really helpful to get me started.

    I’ve also got http://www.sqlcourse.com/ bookmarked because it looks really promising. Good luck!

    #720

    Ben Carter
    Participant

    Jolene,

    Thanks so much. I think I checked these out earlier (when you posted them on Twitter?). I looked again. They didn’t help much on the relationship aspect. I think I may have figured it out. I think that I need to use INNER, OUTER, CROSS, etc. JOINs instead of just JOIN. I was trying to use LibreOffice Base as  a GUI and there is no way (except coding in the SQL window, of course) to do this. Will check it out again.

    Thanks again.

    Cheers,

    Ben

    #727

    Eric Kansa
    Participant

    I have no idea how your data are structured and what your tables are. So not knowing your schema, it’s hard to respond to this. But, there are often many, many ways to relate data using SQL. Besides JOINs there are sub-queries (see: http://www.postgresqltutorial.com/postgresql-subquery/). Some of these approaches work differently on different databases, so fiddling and experimenting seems unavoidable.

    Sorry to not be very helpful! It’s super hard for me as an outsider to give more specific advice on this issue.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.

Close

Account Activated

Your account was activated successfully! You can now log in with the username and password you provided when you signed up.

Close

Account sign-in

Please use the form below to sign-in to your account.

Forgot password?
Close

Recover password

Please enter your username or email address. You will receive a link to create a new password via email.

We've sent you an activation link. Please check your inbox.

Close

Account signup

1 Account Info

2 Personal Info

Registering for this site is easy, just fill in the fields below and we'll get a new account set up for you in no time.

In order to avoid spam, automatic account registration is restricted to emails from the following domains – .edu, .org, .gov. To register with a different email address, please write to digitalarchaeology@matrix.msu.edu to request an account.

Success!

A confirmation link has been emailed to you

Skip to toolbar