Questions tagged [database-design]
Database design is the process of specifying the logical and/or physical parts of a database.
40 questions
1
vote
0
answers
148
views
Managing PostGIS many-to-many relationships between several tables with different geometry types from QGIS
I have this data set stored in a PostGIS (16+3.4) database:
Each category of items being stored in its own table:
CREATE TABLE public.rock
(
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
...
- 10.9k
0
votes
0
answers
47
views
Joining Excel to attribute table
Im working with ArcGIS Desktop (ArcMap), trying to create a geodatabase.
So I have a bunch of coordinate points to create polygons and an excel full of attribute data about those polygons.
What ...
- 101
0
votes
0
answers
51
views
In a Network is Valve a Line or Point in GIS Standard?
I am designing a GIS Database related to Gas Network. In my opinion Valve equipment is a link equipment which can be closed or opened, So it should be a Line. But lots of clients gather this data as ...
0
votes
1
answer
2k
views
Adding“last modified” and “created” column in SQLite/SpatiaLite table
I am designing a new db schema for a SQLite/SpatiaLite database.
Each table should get two extra columns called modified and created which should be automatically change as soon a row gets inserted ...
- 29
1
vote
0
answers
321
views
PostGIS: proper way to model object that follows sections of another object's path?
First, apologies if this has been asked before. I don't really know how/what to search for, and am struggling to just put this question into words.
I'm trying to model aerial utilities in a PostGIS ...
- 173
3
votes
0
answers
924
views
Postgis : Geometry vs 3 geom columns in single table
Requirement:
There a specific requirements where I need a geometry column in table to be flexible ( It can line or point or polygon or all of it )
Justification for requirements :
main concern is ...
- 2,770
9
votes
3
answers
2k
views
Can primary keys and foreign keys be used in versioned geodatabases?
I've been brushing up on relational database design theory, and I keep coming across primary keys (PKs) and foreign keys (FKs).
PKs and FKs are said to be pillars of relational database design and ...
- 1,272
1
vote
0
answers
82
views
Database Table in SQL Server
I have an excel table. I created a database table in SQL Server database platform in ArcMap. I added fields, fields' data type, and domains to some data fields (columns). When I open the table, all I ...
- 35
6
votes
2
answers
3k
views
How do partition tables improve query performance in PostGIS? [closed]
I have static road, country and state datasets in my PostGIS database. Road networks are partitioned by their containing country. Countries, States and other areas reside in their own table and are ...
- 259
1
vote
0
answers
46
views
GIS Workorder System [closed]
First off, these questions are relevant:
https://dba.stackexchange.com/questions/7900/pros-and-cons-of-having-one-table-per-entity-vs-having-a-shared-table-with-an-e/7907#7907
https://dba....
- 414
2
votes
1
answer
237
views
Cloud based database solution [closed]
I'm looking for a cloud based database solution where I can store about 1 GB worth of tabular data and both myself and my client can access to perform SQL queries. I'm looking for this database to ...
- 547
7
votes
1
answer
482
views
GeoJson structure
I currently designing a table/document database structure and use GeoJson to store points and routes. Currently I choose MongoDb, I will consider PostGIS later.
I want to store route info for public ...
- 337
0
votes
1
answer
6k
views
Adding a column of WKT format in the table (postgreSQL)
I want to include this column of data that are in WKT format (they are mostly straight lines). What data type should I use?
I need to create this database for mapping. There are about 30000 features ...
- 77
2
votes
2
answers
2k
views
How are section of roads uniquely identified (in both directions) by Google Maps?
I am wondering whether roads around the world are uniquely identified by a certain value in Google Maps? If so, are they mapped uniquely in terms of their direction? i.e is route A to B is identified ...
- 33
1
vote
2
answers
196
views
Seeking web service that returns elevation profile between two points and can process 5000 requests per second
I need a web service that provides elevation profile data between two coordinates with request rates at a minimum of 2000 requests per second but preferably at 5000 per second. The distance between ...
- 113
0
votes
1
answer
122
views
Creating spatial database with many categories that have same set of values?
I am working on creating a spatial database of county agricultural data where each county has multiple crops (30+) and for every crop there are several production metrics (yield, harvested area, ...
- 103
12
votes
4
answers
2k
views
How to model a graveyard - one point per deceased or one per grave? [closed]
This is a continuation of my previous question about our project on how to economically get away with the implementaion of a graveyard in a GIS system…
On the graveyard we can find
Regular ...
6
votes
1
answer
359
views
Spatial+Temporal database request for 4D intersection
Here is the function I need to implement. My application deals with mobile objects with variable position and range (think of radar range). As I can't test each minute if n mobiles detect each other (...
- 259
3
votes
1
answer
2k
views
How to build a relational database system with 4 tables in arcgis desktop?
I have been given data from an working online application which consists of a relational model. My aim is to recreate the model using Arcgis Desktop.
I have the table "elements" containing Point ...
- 10.3k
1
vote
1
answer
104
views
Strategies for managing (and ultimately merging) duplicate business locations in a spatial database
I am working on a project right now that involves using multiple (very large) "location directories" to identify with the highest possible inclusiveness, a diverse set of business locations.
These ...
- 1,834
7
votes
3
answers
672
views
Roadmap to creating a GIS? [closed]
Got asked that today in a job interview, I replied with the usual :
Gather Data : what data do we want, do we possess it ? If not how to do we acquire it, etc.
Database : after we have successfully ...
1
vote
1
answer
600
views
QGIS Value Map Edit Widget with properties dependent on another field
I'm having a problems with Edit Widgets in QGIS. I'm working on a GIS Database for archaeological data, where every site is represented by a polygon. In the attributes there is a field for the era ...
- 11
3
votes
2
answers
392
views
Traversing a PostGIS table tree
I'm developing a geospatial db for a company that operates at a number of large facilities. Its primary purpose is to help staff locate themselves and equipment within each facilities' multi-storey ...
- 363
6
votes
2
answers
458
views
Setting up foundational OSGeo enterprise GIS? [closed]
First let me describe the task:
My lab has approached me about setting up a simple GIS for a colleagues 'non-spatial' lab. There are a couple of key characteristics to the intended setup:
Fully open ...
- 172
5
votes
2
answers
401
views
Is adding a new table for every layer a good design idea?
Is it a good design idea to maintain separate tables for each layer in a PostGIS database?. Every layer is defined by users. A user creates his/her layer say ATM location. That's meant by defined by ...
- 1,761
7
votes
2
answers
2k
views
Should I use ISO 3166-1 Numeric codes as Primary Keys for Countries in a database table?
My main concern is, will one country ever adopt a numeric code that has been used for another in the past, such as can happen with alphanumeric 3166-1 codes? If not, then it seems like a perfect ...
- 173
0
votes
2
answers
182
views
creating one tree to many observations in qgis
Looking to create a database for QGIS where each point represents a tree (with name attribute) and another table of observations about each tree over time. Ideally I can update tree location in QGIS ...
- 11
3
votes
2
answers
12k
views
How to design a database for continents, countries, regions, cities and POIs?
I'm brand new to GIS programming and I am designing a GIS application. Target is to create system with continents, countries, regions (including states, sub-regions, provinces), cities and places in ...
3
votes
4
answers
5k
views
Creating unique IDs for file geodatabase using ArcGIS for Desktop?
I use ArcEditor licence level, ArcGIS 10.0 version, and we're talking about a file geodatabase.
I have several feature classes (electric energy domain) and I want to connect them using relationship ...
- 1,476
4
votes
2
answers
1k
views
What's a good way store borehole data in SQLite / SpatiaLite?
I have a bunch of bores / wells with downhole data. The 'header' info for each borehole can easily go into a single table, but I'm wondering how best to store the downhole log data. Typically they are ...
- 466
0
votes
2
answers
3k
views
Is there a way to have a single point feature with multiple records?
Is there a way to have a single point feature and have multiple attributes records associated with it? Multipoint approach won't work as it gives me one attribute set (database row). I am trying to ...
- 871
2
votes
1
answer
203
views
Attributes that characterize a geodetic track
I need a way to index and retrieve a large amount of track data. To do this I need to derive from every track as many attributes as I can. For now, I derive
Name
Tortuosity
Speed (average, max)
...
- 381
1
vote
1
answer
2k
views
Importing/copying geodatabase schema from multiple tables to single table?
I have an existing ArcGIS 10 geodatabase which has a complicated schema containing multiple tables and joins, and I wish to simplify this for faster web delivery.
For example, Featureclass1 is ...
- 21.2k
14
votes
4
answers
3k
views
Database design: road topology in GIS
Is there a standard or a best practice way to store certain features about roads:
Road directionality:
One way streets: Roads going in one direction only
Center Turning lanes: roads that exist ...
- 8,563
5
votes
4
answers
665
views
Record Date and User for an edited feature
The client has a Feature Class in SDE 9.3.1. When a feature in the feature class is edited the client wants to automatically record the date/time and who edited it in the feature class attributes.
...
- 1,563
3
votes
2
answers
2k
views
Sharding of geospatial data
Reading through Rosenberg & Mateos' The Cloud at Your Service I stumbled upon the concept of database sharding.
Authors point out to several advantages of such approach including high (online) ...
user173
11
votes
2
answers
2k
views
Designing Spatial Database for temporal Data? [closed]
I am working on a Weather based GIS App.
I have the data from several weather stations, and this data will updated daily (by a web service).
The obstacles I am facing are:
There are currently 40 ...
- 41.6k
11
votes
1
answer
724
views
Is it possible to maintain primary keys on versioned featureclasses without arcobjects code?
Since it is not a good idea to use ObjectIDs as primary keys in relationships, some other column will need to be used. Is it possible to use DBMS sequences to populate primary key columns in ...
- 25.8k
14
votes
2
answers
2k
views
When is it safe to use ObjectIDs as keys in a relationship class?
I've always avoided using ObjectIDs when defining relationship classes. It is my understanding that some actions on a geodatabase will re-set objectIDs (like import/export). However I do see a lot ...
- 25.8k
16
votes
5
answers
3k
views
Is good database design less important for spatial databases?
I have a strong feeling that database design and normalisation often comes in second hand when dealing with spatial data.
With software costing a fortune and databases with over 100 fields tables I ...
- 13.3k