The fudgeo's discuss from realiii

check triggers
add table optionally (on by default)
remove table entry on drop but check that ogr_contents exists

escape when non-word characters present

Geometry Column name on Feature Class object

add field and field names properties

Utilize Timestamp in tests

add timestamp type to fields in tests
update implementation as required

Support for keyword column names

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?

Derive Envelope

if empty envelope and not empty geometry generate new envelope on property access
handle nan values
applies to all but points
set envelope code

Modernize project structure

change to toml and build

Tune-up Drop / Overwrite to account for Spatial Indexes

Update the bounding coords in the gpkg_contents

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)

Release v0.3.5

Lazify / Delay Connection Open

On Geopackage delay opening the connection until the connection is needed

Release v0.3.7

Update Requirements

Add spatial indexing

add functions

on create feature class

optional, off by default
update internal tables (req 75 and req 76)
create virtual table (req 77)
create triggers (req 77)

on existing feature class

update internal tables (req 75 and req 76)
create virtual table (req 77)
create triggers (req 77)
populate virtual table (Load Spatial Index Values)

Push new package to pypi

update metadata / doc
rebuild (test)
rebuild (prod)

Reduce concatenation and byte array creation

Provide Access to Geometry Type

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()

Release v0.3.6

Use double quotes for escaping

Timestamp/Datetime issue?

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?

Release 0.3.10

Add repr to main objects

Change default for OGR contents to False

Creating tables/feature classes with protected keywords

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 (

fudgeo/fudgeo/sql.py

Line 61 in def68ec

CREATE_FEATURE_TABLE = """

) to this:

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.

registration
__all__

version number -- 10300
improve handling of geometry header

realiii / fudgeo Goto Github PK

fudgeo's Issues

Recommend Projects

Recommend Topics

Recommend Org