Llama-3.2-1B-Instruct-Spatial-SQL-1.0/README.md

---
license: llama3.2
base_model: meta-llama/Meta-Llama-3.2-1B
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-1B-Instruct-Spatial-SQL-1.0, is an 1B, 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 four primary geographic functions released in version 1.0.

**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 |

### 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>

### 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?


### llama.cpp / Hyperparameter Recommendations For Inference
max context ~ 8,000 or lower
<p></p>
top k ~ 100 or lower
<p></p>
temp ~ .4-.7 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: 3e-05
- train_batch_size: 10
- eval_batch_size: 3
- distributed_type: multi-GPU
- num_devices: 2
- optimizer: Adam 8bit
- lr_scheduler_type: linear
- num_epochs: 20

### Training results

| Training Loss | Step | Validation Loss |
|:-------------:|:----:|:---------------:|
| 1.9106        |  10  |   1.9183        |
| 1.1674        |  20  |   1.1710        |
| 0.7446        |  30  |   0.7589        |
| 0.6143        |  40  |   0.6220        |
| 0.5773        |  50  |   0.5695        |
| 0.5328        |  60  |   0.5334        |
| 0.5146        |  70  |   0.5096        |
| 0.4895        |  80  |   0.4925        |
| 0.4893        |  90  |   0.4772        |
| 0.4710        |  100 |   0.4612        |
| 0.4572        |  110 |   0.4438        |
| 0.4358        |  120 |   0.4233        |
| 0.4002        |  130 |   0.4014        |
| 0.3812        |  140 |   0.3768        |
| 0.3461        |  150 |   0.3492        |
| 0.3222        |  160 |   0.3202        |
| 0.3043        |  170 |   0.2921        |
| 0.2727        |  180 |   0.2651        |
| 0.2378        |  190 |   0.2403        |
| 0.2229        |  200 |   0.2184        |
| 0.2121        |  210 |   0.1990        |
| 0.1891        |  220 |   0.1834        |
| 0.1867        |  230 |   0.1723        |
| 0.1848        |  240 |   0.1654        |
| 0.1732        |  250 |   0.1631        |




### Framework versions

- Transformers 4.45.1
- Pytorch 2.5.0
- peft 0.13.2
- Datasets 3.0.1
- Tokenizers 0.20.1