geobabbler

name

description

postgis-spatial-analysis

Perform point-in-polygon spatial analysis on PostGIS databases to determine which points fall within which polygons and generate summary reports. Use when working with PostGIS databases for spatial queries that assign points to polygons, analyze geographic relationships between point and polygon tables, or generate summaries of spatial distributions. Handles SRID transformations, spatial index checks, and optimized query generation.

PostGIS Spatial Analysis

Overview

Perform point-in-polygon analysis on PostGIS databases to assign points from one table to polygons in another, handling coordinate system transformations and generating summary reports.

geobabbler

import geopandas as gpd
from shapely.geometry import Point
import pandas as pd
from datetime import datetime
import os
import shutil
import zipfile

%pip install geopandas shapely geojsonio

geobabbler

WITH geodata AS (
SELECT
	ROW_NUMBER() OVER () AS OBJECTID,
    *
FROM
    read_parquet('~/Downloads/IRL.parquet') LIMIT 1000)
select json_object(
'type', 'FeatureCollection',
    'features', array_agg(
        json_object(

geobabbler

import requests
import openai
import urllib
import json
from duckduckgo_search import DDGS

# Set your OpenAI API key here
openai.api_key = "your-openai-api-key"

def search_duckduckgo(query):

geobabbler

-- public.image_embeddings definition

-- Drop table

-- DROP TABLE public.image_embeddings;

CREATE TABLE public.image_embeddings (
	id serial4 NOT NULL,
	image_path text NULL,
	embedding public.vector NULL,

geobabbler

import os
import psycopg2
import numpy as np
from PIL import Image
from scipy.spatial.distance import cosine
import torch
from torchvision import models, transforms

# Database configuration
DB_NAME = "database"

geobabbler

import os
import psycopg2
from PIL import Image
import torch
from torchvision import models, transforms

# Database configuration
DB_NAME = "database"
DB_USER = "user"
DB_PASSWORD = "password"

geobabbler

import requests
from bs4 import BeautifulSoup
import os

qry = "glacier" #wikimedia commons query
# Define the search URL
search_url = f"https://commons.wikimedia.org/w/index.php?search={qry}&title=Special:MediaSearch&go=Go&type=image"

headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36"

geobabbler

import pdfkit
import requests

'''
The current theme will be applied to outputs, so it is recommended to switch to a simple theme before exporting.
'''

#Generate PDF from individual post URL
def url_to_pdf(url, output_filename):
    try:

geobabbler

SELECT
  ARRAY_TO_STRING(ARRAY_AGG(CONCAT(st_x(q.loc),',',st_y(q.loc))), ';') AS coords
FROM (
  WITH
    DATA AS (
    SELECT
      loc,
      resource_id,
      wkt,
      capture_date,