## ----include = FALSE----------------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

## ----setup--------------------------------------------------------------------
library(lazysf)
library(dplyr)

## ----sqlite-basics------------------------------------------------------------
f <- system.file("extdata/nc.gpkg", package = "lazysf", mustWork = TRUE)
lf <- lazysf(f)

## filter + select + arrange
lf |>
  filter(AREA < 0.1) |>
  select(NAME, AREA, geom) |>
  arrange(AREA) |>
  collect()

## ----sqlite-agg---------------------------------------------------------------
lf |>
  group_by(SID74) |>
  summarise(n = n(), mean_area = mean(AREA, na.rm = TRUE)) |>
  arrange(desc(n)) |>
  head(5) |>
  collect()

## ----gdal-native, eval = FALSE------------------------------------------------
# ## Measurements and accessors
# lf |>
#   mutate(
#     area = st_area(geom),
#     srid = st_srid(geom)
#   ) |>
#   head(5) |>
#   collect()

## ----spatialite, eval = FALSE-------------------------------------------------
# ## Spatial predicates
# lf |>
#   filter(st_intersects(geom, st_geomfromtext("POLYGON((-80 35, -79 35, -79 36, -80 36, -80 35))"))) |>
#   collect()
# 
# ## Geometry operations
# lf |>
#   mutate(
#     wkt = st_astext(geom),
#     centroid = st_centroid(geom),
#     buffered = st_buffer(geom, 0.01)
#   ) |>
#   collect()
# 
# ## Coordinate transformation
# lf |>
#   mutate(geom_3857 = st_transform(geom, 3857)) |>
#   collect()

## ----check-spatialite, eval = FALSE-------------------------------------------
# con <- dbConnect(GDALSQL(), f)
# tryCatch(
#   DBI::dbGetQuery(con, "SELECT spatialite_version()"),
#   error = function(e) message("SpatiaLite not available")
# )

## ----ogrsql, eval = FALSE-----------------------------------------------------
# lf_ogr <- lazysf(f, dialect = "OGRSQL")
# 
# ## Basic filter/select work
# lf_ogr |>
#   filter(AREA < 0.1) |>
#   select(NAME, AREA) |>
#   collect()

## ----direct-sql---------------------------------------------------------------
lazysf(f, query = "SELECT NAME, AREA, geom FROM nc WHERE AREA < 0.1 ORDER BY AREA") |>
  collect()

## ----no-window, eval = FALSE--------------------------------------------------
# ## This won't work:
# lf |> slice_min(AREA, n = 5)
# 
# ## Do this instead:
# lf |> arrange(AREA) |> head(5) |> collect()

## ----arrow, eval = FALSE------------------------------------------------------
# lf <- lazysf(f, use_arrow = TRUE)
# d <- lf |> collect()

## ----geom-formats, eval = FALSE-----------------------------------------------
# ## WKB (default) — compact binary, interoperable with sf and wk
# lazysf(f, geom_format = "WKB")
# 
# ## WKT — human-readable text
# lazysf(f, geom_format = "WKT")
# 
# ## BBOX — bounding box per feature (fast, no full geometry)
# lazysf(f, geom_format = "BBOX")
# 
# ## NONE — no geometry at all (fastest for attribute-only queries)
# lazysf(f, geom_format = "NONE")

## ----debug, eval = FALSE------------------------------------------------------
# options(lazysf.query.debug = TRUE)
# lazysf(f) |> filter(AREA < 0.1) |> collect()
# ## prints: SQL and row counts for each query dbplyr issues