| --- |
| license: llama3.2 |
| base_model: meta-llama/Meta-Llama-3.2-3B |
| language: |
| - en |
| pipeline_tag: text-generation |
| tags: |
| - code |
| - spatial |
| - sql |
| - GIS |
| - PostGIS |
| --- |
| |
| **ENGLISH ONLY - Use 8b models for alternate languages.** |
|
|
| ### Model Information |
|
|
| This model, Llama-3.2-3B-Instruct-Spatial-SQL-1.1, is an 3B, narrow use case, text to spatial SQL, lightly fine-tuned model. In general, its primary use case |
| is the Natural Language command adaptation of particular geographic spatial functions as normally defined in pure SQL. Data input should be a combination of an English prefix in the form of a question, and a coordinate prompt injection, likely from an active mapping system application coordinate list. Output is PostGIS spatial SQL. |
|
|
| There are five primary geographic functions released in version 1.1. |
|
|
| **Model developer**: Mark Rodrigo |
|
|
| **Github**: https://github.com/mprodrigo/spatialsql |
|
|
| **Model Architecture**: The model is a QLoRA / Supervised Fine Tuning (SFT) |
|
|
| ### Model Input / Output Overview: |
|
|
| Input: Text plus coordinate prompt injection. |
| </br> |
| Output: **PostGIS spatial SQL** |
| </br> |
| NOTE: Inputs and outputs are in meters and or geographic decimal degrees WGS 84 coordinates. |
|
|
| | Function | Question Input | Geo Input | SQL Execution Output | |
| |:---------:|:----------------:|:---------:|:-------------------------:| |
| | Area | Area question | Polygon | Number - Area sq meters | |
| | Centroid | Center question | Polygon | Point | |
| | Buffer | Buffer distance | Point | Polygon | |
| | Length | Length question | Line | Number - Length in meters | |
|
|
| </br> |
|
|
| | Function | Question Input | Geo Input 1 | Geo Input 2 | SQL Execution Output | |
| |:---------:|:----------------:|:-----------------------:|:-----------------------:|:-----------------------------------------------| |
| | Distance | Distance question| Point, Line, or Polygon | Point, Line, or Polygon | Number - Distance between geometries in meters | |
|
|
| ### Example Prompt / Prompt File |
|
|
| <|begin_of_text|><|start_header_id|>system<|end_header_id|> |
| <p></p> |
| You are a helpful assistant. You are an expert at PostGIS and Postgresql and SQL and psql. |
| <p></p> |
| <|eot_id|><|start_header_id|>user<|end_header_id|> |
| |
| \### Instruction: Write a PostGIS SQL statement for the following. |
| <p></p> |
| |
| \### Input: |
| |
| <p></p> |
| {input} |
| |
| <p></p> |
| |
| \### Response: |
| |
| <|eot_id|><|start_header_id|>assistant<|end_header_id|> |
|
|
|
|
| ### Examples |
|
|
|
|
| AREA |
| <p></p> |
|
|
| \### Input: What is the area for the polygon? : 'Polygon ((-3.7515154 40.3855551, -3.7514972 40.3856581, -3.7507005 40.3855767, -3.7507167 40.3854722, -3.7515154 40.3855551))' |
| <p></p> |
|
|
| \### Response: SELECT ST_Area(geog) As area FROM (select 'Polygon ((-3.7515154 40.3855551, -3.7514972 40.3856581, -3.7507005 40.3855767, -3.7507167 40.3854722, -3.7515154 40.3855551))' :: geography geog) subquery; |
| <p></p> |
| |
| CENTROID |
| <p></p> |
| |
| \### Input: What is the centroid for the polygon? : 'Polygon ((-3.6934636 40.4808785, -3.6933352 40.4811486, -3.6930125 40.4810598, -3.693141 40.4807897, -3.6934636 40.4808785))' |
| <p></p> |
| |
| \### Response: SELECT ST_AsText(ST_Centroid(geog)) As centroid FROM (select 'Polygon ((-3.6934636 40.4808785, -3.6933352 40.4811486, -3.6930125 40.4810598, -3.693141 40.4807897, -3.6934636 40.4808785))' :: geography geog) subquery; |
| |
| BUFFER |
| <p></p> |
| |
| \### Input: What is the thousand meter buffer for the following point? : 'Point(-8.7522658 41.3862664)' |
| <p></p> |
| |
| \### Response: SELECT ST_AsText(ST_Buffer(geog, 1000)) as buffer FROM (select 'Point(-8.7522658 41.3862664)' :: geography geog) subquery; |
| |
| <p></p> |
| LENGTH |
| <p></p> |
| |
| \### Input: How long is the line? : 'LINESTRING (-3.6976693 40.4263178, -3.6986082 40.4258729)' |
| <p></p> |
| |
| \### Response: SELECT ST_Length(geog) As length FROM (select 'LINESTRING (-3.6976693 40.4263178, -3.6986082 40.4258729)' :: geography geog) subquery; |
| <p></p> |
| DISTANCE |
| <p></p> |
|
|
| \### INPUT: How far apart is the point and line? : 'Point(-109.87549823 38.60574249)' 'LineString(-109.24324628 38.76349931, -109.4821773 38.6875815)' |
| <p></p> |
|
|
| \### Response: SELECT ST_Distance(gg1, gg2) As distance FROM (select 'Point(-109.87549823 38.60574249)' :: geography gg1, 'LineString(-109.24324628 38.76349931, -109.4821773 38.6875815)' :: geography gg2) subquery; |
| <p></p> |
| |
| ### A Few Known Question Variation Examples |
| |
| <p></p> |
| AREA |
| <p></p> |
| What is the area for the geometry? |
| <p></p> |
| What is the area for this polygon? |
| <p></p> |
| CENTROID |
| <p></p> |
| What is the centroid for the geometry? |
| <p></p> |
| What is the center point of the polygon? |
| <p></p> |
| BUFFER |
| <p></p> |
| What is the 100 meter buffer for the following point? |
| <p></p> |
| Buffer the following point a thousand meters. |
| <p></p> |
| What is the 1000 meter buffer for the following point? |
| <p></p> |
| LENGTH |
| <p></p> |
| What is the length of the line? |
| <p></p> |
| How long is this line? |
| <p></p> |
| DISTANCE |
| <p></p> |
| The distance between the polygon and the line is? |
| <p></p> |
| What is the distance between the points? |
| <p></p> |
| How far apart are the two lines? |
| |
| ### llama.cpp / Hyperparameter Recommendations For Inference |
| max context ~ 8,000 or lower |
| <p></p> |
| top k ~ 100 |
| <p></p> |
| temp ~ .4-.5 or lower |
| |
| ### Agent Considerations |
| Agents are being considered as a separate project. Agents would mostly be related to pulling the coordinates from a mapping UI, and executing the SQL from responses against a PostGIS database. |
| |
| ### Further Reference - link this |
| https://postgis.net/docs/manual-3.3/PostGIS_Special_Functions_Index.html#PostGIS_GeographyFunctions |
| |
| ### Evaluation data |
| More information needed |
| |
| ### Training data |
| Custom synthetic |
| |
| ### Training hyperparameters |
| |
| The following hyperparameters were used during training: |
| - learning_rate: 2e-4 |
| - train_batch_size: 10 |
| - eval_batch_size: 3 |
| - distributed_type: multi-GPU |
| - num_devices: 2 |
| - optimizer: Adam 8bit |
| - lr_scheduler_type: linear |
| - num_epochs: 5 |
| |
| ### Training results |
| |
| | Training Loss | Step | Validation Loss | |
| |:-------------:|:----:|:---------------:| |
| | 0.8115 | 10 | 0.6781 | |
| | 0.6433 | 20 | 0.6173 | |
| | 0.6182 | 30 | 0.5445 | |
| | 0.4994 | 40 | 0.4888 | |
| | 0.4911 | 50 | 0.4265 | |
| | 0.4791 | 60 | 0.3477 | |
| | 0.3605 | 70 | 0.3096 | |
| | 0.3493 | 80 | 0.2850 | |
| | 0.3344 | 90 | 0.2604 | |
| | 0.1718 | 100 | 0.2508 | |
| |
| |
| |
| |
| ### Framework versions |
| |
| - Transformers 4.46.1 |
| - Pytorch 2.5.1 |
| - peft 0.13.2 |
| - Datasets 3.1.0 |
| - Tokenizers 0.21.0 |
