7 ms·
Fun fact that was dredged up because the author mentions Australia: GPS points change. Their example coordinates give 6 decimal places, accurate to about 10-15c
by Dachande663 1y ago
Fun fact that was dredged up because the author mentions Australia: GPS points change. Their example coordinates give 6 decimal places, accurate to about 10-15cm. Australia a few years back shifted all locations 1.8m because of continental drift they’re moving north at ~7cm/year). So even storing coordinates as a source of truth can be hazardous. We had to move several thousand points for a client when this happened.
- atoav 1y agoIn the past year or so I have thought a lot about how to design tables and columns within databases and there is nearly nothing that wouldn't get more robust by adding in a "valid_from" and "valid_till" and make it accept multiple values. Someone's name is Foo? What if they change it to Bar at some point and you need to access something from before with the old name? If you have only a name field that has a single value that is going to be a crazy workaround. If your names are referencing a person with a date that is much easier. But you need to make that ddcision pretty early.
- pavel_lishin 1y agoIf you have an "audit" table, where you write a copy of the data before updating it in the primary table, that's a decision you can make at any point. Of course, you don't get that historical data, but you do get it going forward from there.
- tough 1y agosomething like https://www.pgaudit.org/ https://www.pgaudit.org/ ? Basically you keep an history of all changes so you can always roll-back / get that data if needed?
- pavel_lishin 1y agoThe last time we did this, we basically hand-rolled our own, with a database trigger to insert data into a different table whenever an `UPDATE` statement happened. But this seems like it's probably a better solution.
- tough 1y agonever had used pgaudit yet to vouch for it but have it on the backburner/log of things to try for such a use case! I think the real magic is it lleverages the WAL (write ahead logs) from pg engine itself, which you could certainly hook up into too, but im not a db expert here
- tough 1y agoI just found out about bemi dot io, seems like they're targeting this issue
- homebrewer 1y agoSQL 2011 defines temporal tables, which few FOSS databases support. I used it in mariadb: https://mariadb.com/kb/en/temporal-tables/ https://mariadb.com/kb/en/temporal-tables/ and if your schema doesn't change much, it's practically free to implement, much easier and simpler than copypasting audit tables, or relying on codegen to do the same.
- boramalper 1y agoSee also "Eventual Business Consistency"[0] by Kent Beck. Really good read. > Double-dated data—we tag each bit of business data with 2 dates: > * The date on which the data changed out in the real world, the effective date. > * The date on which the system found out about the change, the posting date. > Using effective & posting dates together we can record all the strange twists & turns of feeding data into a system. [0] https://tidyfirst.substack.com/p/eventual-business-consistency https://tidyfirst.substack.com/p/eventual-business-consisten...
- atoav 1y agoThanks for posting this, I read tbis a while ago, but it is worth revisiting.
- jandrewrogers 1y agoThe tradeoff is that this is very expensive at the scale of large geospatial data models both in terms of performance and storage. In practice, it is much more common to just take regular snapshots of the database. If you want to go back in time, you have to spin-up an old snapshot of the database model. A less obvious issue is that to make this work well, you need to do time interval intersection searches/joins at scale. There is a dearth of scalable data structures and algorithms for this in databases.
- nradov 1y agoAnyone who works with human names should take a look at the HL7 V3 and FHIR data models, which were designed for healthcare. They support name validity ranges, and a bunch of other related metadata. It can be challenging to efficiently represent those abstract data models in a traditional traditional database because with a fully normalized schema you end up needing a lot of joins.
- taeric 1y agoOne that routinely surprises me is that this is not easy to do in any popular contacts application. I actually would like to keep every address I've ever had in there, in case I need to remember it for some reason later. Maybe I just want to reminisce. I don't want to accidentally have it as an "active" one, though.
- AlotOfReading 1y agoGPS coordinates actually account for the motion of the Earth's tectonic plates. The problem is that it's a highly approximate model that doesn't accurately reflect areas like Australia very well. There's a great visualizer of the coordinate velocity from the Earthscope team: https://www.unavco.org/software/visualization/GPS-Velocity-Viewer/GPS-Velocity-Viewer.html https://www.unavco.org/software/visualization/GPS-Velocity-V...
- meindnoch 1y ago>GPS coordinates actually account for the motion of the Earth's tectonic plates. What?
- 867-5309 1y agoaccounts for (something), phrasal verb meaning "considers; incorporates; takes on board" as opposed to the more obvious "gives rise to; is responsible for". I had to read twice too
- meindnoch 1y agoYeah, I know what "accounts for" means. I just can't comprehend how GPS coordinates could account for the tectonic plates' motion. Never heard of such a thing, and can't see how it would work on a conceptual, mathematical level.
- deleted 1y ago[deleted]
- n4r9 1y agoThey don't, and you're right it wouldn't make sense.
- janzer 1y agoI'm pretty positive that is showing the reverse, i.e. how much a given "location" is moving using gps coordinates. Not adjusting the gps coordinates to refer to a constant "location".
- xucheng 1y agoCan this be solved by storing a timestamp of the record along with precise GPS coordinates? Could we then utilize some database to compute the drift from then and now?
- haneefmubarak 1y agoI mean, certainly - if you store both GPS time and derived coordinates from the same sampling, then you can always later interpret it as needed - whether relative to legal or geographical boundaries etc as you might want to interpret in the future.
- jandrewrogers 1y agoYes, in fact it should essentially be mandatory because the spatial reference system for GPS is not fixed to a point on Earth. This has become a major issue for old geospatial data sets in the US where no one remembered to record when the coordinates were collected. To correct for these cases you need to be able to separately attribute drift vectors due to the spatial reference system, plate tectonics, and other geophysical phenomena. Without a timestamp that allows you to precisely subtract out the spatial reference system drift vector, the magnitude of the uncertainty is quite large.
- deleted 1y ago[deleted]
- omcnoe 1y agoYou don’t need to store a timestamp, but the local coordinate reference system that the coordinates are in. When revisions like this are made, it’s by updating the specification of a specific local coordinate reference. WGS84 is global, but for most precise local work more specific national coords are used instead.
- pgreenwood 1y agoSee https://spatialreference.org/ https://spatialreference.org/
- pavel_lishin 1y agoDamn! 7cm per year feels blazing fast when you consider the fact that it's a whole continent.
- anotherevan 1y agoWe're coming for you!
- XorNot 1y agoI mean I'm still mind blown that the Three Gorges dam in China literally changed the rotational speed of the Earth, and thus the length of the day.
- voidUpdate 1y agoYou can change the length of the day just by spinning counterclockwise :P
- niccl 1y agoA way to think about it I've seen a few times: continental drift is roughly the same order of magnitude as the rate your fingernails grow!
- akst 1y agoMy knowledge of geospatial sets is fairly shallow, but I’ve worked a bit with Australian map data and I’m assuming are you referring to the different CRSs, GDA2020 and GDA1994? I’d imagine older coordinates would work with the earlier CRS? But I can understand not all coordinates specify their CRS. This have really been an issue for me personally, but I’ve mostly worked with NSW spatial and the Australian Bureau of statistics geodata.
- jandrewrogers 1y agoEven accounting for tectonic drift, there is a concept of positioning reproducibility that is separate from precision. In general the precision of the measurements is much higher than the reproducibility of the same measurements. That is, you may be able to measure a fixed point on the Earth using an instrument with 1cm precision at a specific point in time but if you measure that same point every hour for a year with the same instrument, the disagreement across measurements will often be >10cm (sometimes much greater), which is much larger than e.g. tectonic drift effects. For this reason, many people use the reproducibility rather than instrument precision as the noise floor. It doesn’t matter how precise an instrument you use if the “fixed point” you are measuring doesn’t sit still relative to any spatial reference system you care to use.
- Robotbeat 1y agoThe whole accuracy vs precision thing.
- jandrewrogers 1y agoRelated but slightly different. The accuracy is real but it is only valid at a point in time. Consequently, you can have both high precision and high accuracy that nonetheless give different measurements depending on when the measurements were made. In most scientific and engineering domains, a high-precision, high-accuracy measurement is assumed to be reproducible.
- hnaccount_rng 1y agoI think this is a charitable interpretation of the remark which deprives GP from learning something (sorry if this comes across as condescending, I'm genuinely trying to point out a imo relevant difference) No it's not at all accuracy vs precision. That statement is about a property of the measurement tool, where one can have systematic offsets [0] (think about a manual clock, where the manufacturer clued the finger on with a slight shift) vs they can simply be inaccurate (think about a clock that only has a minute finger, but not one for seconds). The thing pointed out by the original comment is about a change in the _measured_ system. Which is something fundamentally different. No improvement in the measurement tool [1] can help here as its reality that changes. Even writing down the measurement time is only going to help so much since typically you aren't interested in precisely the time of measurement and will do an implicit assumption of staticness of the real world. [0] The real reason for those is that it is _much_ simpler to build a precise relative measurement tool (i.e. it's easier to say "bigger than that other thing" than "this large"). One example is CO2 concentration measurements, they are often relative to outdoor CO2, which is - unfortunately - not stable [1] Assuming that the tool is only allowed to work on one point in time. If you include e.g. a weather modelling supercomputer in your definition of tools, that would again work.
- RainyDayTmrw 1y agoThis is one of many reasons why property surveying records use so many seemingly obscure or redundant points of reference. In case anyone wonders why modern property surveying isn't only recording lots of GPS coordinates.
- sleepy_keita 1y agoJapan publishes new CRSes after large earthquakes to account for drift. The M9 earthquake in 2011 recorded a maximum shift of 5 meters!
- cameldrv 1y agoI think Australia has its own datum for this reason that can float against WGS84
- jcattle 1y agoAlmost all continents have their own datum. For Japan there's a special case where they now have 18 geodetic zones. Each zone is defined as parts of the crust that tend to move somewhat homogeneously. Basically after the 2011 Earthquake they had a geodetic mess at their hands, with all coordinates just being all over the place since the ground moved so much. That's why they later on changed their approach.
- timonofathens 1y ago[dead]
- taeric 1y agoThis is a large part of why surveying is done to landmarks.
- pugworthy 1y agoThis is exactly what I was thinking. Except for a tectonic fault line between the point and the landmark, a relative grid of neighboring landmarks as extra reference would be useful as well. Once you establish a location of one point, it gets easier to establish the location of related points.