realiii / fudgeo Goto Github PK
View Code? Open in Web Editor NEWRemoving the fear-uncertainty-doubt from GeoPackages. OGC GeoPackage Support via Python and SQLite
License: MIT License
Removing the fear-uncertainty-doubt from GeoPackages. OGC GeoPackage Support via Python and SQLite
License: MIT License
Hey there!
Got another one for you. I'm trying to add in datetimes using the TIMESTAMP
Field Type, but I'm only getting the year showing up in ArcGIS Pro. I can still select the values from Python and I get back the python datetime object, though.
The other issue I've run into is inserting datetime objects that include a timezone. Those also insert correctly, but only the year shows up in ArcGIS. There's also an issue selecting those values after the fact (it throws a ValueError: invalid literal for int() with base 10: b'01+00'
error).
You can test it out using this:
import pathlib
from datetime import timezone
from faker import Faker
from fudgeo.geopkg import GeoPackage, SpatialReferenceSystem, Field
from fudgeo.enumeration import GeometryType, SQLFieldType
from fudgeo.geometry import Point
from fudgeo.sql import ESRI_4326
fake = Faker()
# Create or connect to GeoPackage
path_gpkg = pathlib.Path.cwd().joinpath("data/fudgeo_tutorial.gpkg")
if path_gpkg.exists():
gpkg = GeoPackage(path_gpkg)
else:
gpkg = GeoPackage.create(path_gpkg)
srs = SpatialReferenceSystem("GCS_WGS_1984", "EPSG", 4326, ESRI_4326)
# Fake up some data
rows = []
for _ in range(100):
lat, lon, city, cc, tz = fake.location_on_land()
lon = float(lon)
lat = float(lat)
rows.append((fake.name(), city, cc, lon, lat, fake.date_object(), fake.date_time(), fake.date_time(tzinfo=timezone.utc), Point(x=lon, y=lat, srs_id=4326)))
# Define fields
fields = (
Field("full_name", SQLFieldType.text),
Field("city", SQLFieldType.text),
Field("country_code", SQLFieldType.text, 2),
Field("longitude", SQLFieldType.float),
Field("latitude", SQLFieldType.float),
Field("date", SQLFieldType.date),
# These next 2 are the ones that have issues:
Field("datetime", SQLFieldType.timestamp),
Field("datetime_tz", SQLFieldType.timestamp),
)
# Create feature class
fc = gpkg.create_feature_class(
"Datetime_Test",
srs,
fields=fields,
shape_type=GeometryType.point,
overwrite=True
)
# Write out the data
with gpkg.connection as conn:
conn.executemany(
"""INSERT INTO Datetime_Test (full_name, city, country_code, longitude, latitude, date, datetime, datetime_tz, SHAPE)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
rows
)
# If you've got ArcGIS Pro, you'll see that the `TIMESTAMP` fields only show the year.
# Now try selecting the datetimes with timezone
with gpkg.connection as conn:
cursor = conn.execute("SELECT datetime_tz FROM Datetime_Test")
features = cursor.fetchall()
This is where the ValueError
is thrown.
With regards to the datetimes, if I change the Field Type to Field("datetime", "DATETIME")
, then format the datetime objects using .strftime("%Y-%m-%dT%H:%M:%S.%fZ")
, ArcGIS Pro displays them properly, and I can select them no problem, but we're back to strings now instead of datetime objects.
Thoughts?
Add tests and profile
return a string that can be used in query for controlling the geometry type, eg PointZ
like in this snippet:
gpkg = GeoPackage(r'c:\data\example.gpkg')
cursor = gpkg.connection.execute(
"""SELECT SHAPE "[PointZ]", heart_rate FROM test""")
features = cursor.fetchall()
read from header if present and expose on geometry objects
Include parameters for testing #12
Could we also add an option to update the bounding coords in the gpkg_contents table as well? That would allow the "zoom to layer" functionality to work in things like QGIS and ArcGIS.
Originally posted by @CF-FHB-X in #6 (comment)
__all__
do some additional profiling to see where improvements can be made.
add numpy
and bottleneck
dependencies
change to toml and build
Me again! Found one last issue. If someone creates a feature class or table that uses one of the SQLite keywords (see https://www.sqlite.org/lang_keywords.html for the full list), the following error is thrown (in this case using or
as the feature class name):
sqlite3.OperationalError: near "or": syntax error
While it's probably not a good idea to use those keywords, users will find a way to break that! In fact, it is possible to create tables with those keywords - you just need to quote the table name in the CREATE TABLE
statements. For example, you can change the CREATE_FEATURE_TABLE
SQL in fudgeo
(
Line 61 in def68ec
CREATE_FEATURE_TABLE = """
CREATE TABLE '{name}' (
fid INTEGER not null primary key autoincrement,
SHAPE {feature_type}{other_fields})
"""
(just quoted the table name)
Either that or find a way of checking for those keywords beforehand, but I can't seem to find a way to do that from the sqlite3
library. There are routines that can do that in sqlite (see https://www.sqlite.org/c3ref/keyword_check.html), but I have no clue how to access them from Python.
Hi there!
I just got fudgeo
incorporated into a package we've been developing, and it's working like a charm. I did have a question regarding overwriting feature classes, though. If we're passing in a list of datasets to write out to GPKG, but there's already a feature class with that name in the GPKG, we get a `table [name] already exists" error. Is there a way to either possibly overwrite a feature class, or delete the existing one? Not sure if something along the lines of this would work:
gpkg = GeoPackage(r'c:\data\example.gpkg')
cursor = gpkg.connection.execute(
"""DROP TABLE test""")
cursor.commit()
Are there any other support tables that would get affected if we went this route?
just unpack the header parts and keep the remainder of the wkb payload around until needed
On Geopackage delay opening the connection until the connection is needed
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.